0

I'm looking to create a formula to pick a value from one workbook based on a number of conditions.

I have a spreadsheet with various prices and discounted values dependent on quantity, level of service and length of time. I'm looking to make a new workbook that references this spreadsheet that when values for three variables is entered, Excel will choose the correct corresponding value.

I'm not sure if I should be using if, or etc

Essentially the logic is:

If Level of service is a (4 options) AND Length of Contract is b (4 options) And Quantity is c (5 options) THEN Price per month is x (Referenced Cell)

I'm not sure what syntax and what formula to use. Any help is greatly appreciated!

I'm using Excel 2013

Many thanks,

Andrew

  • The title says worksheet, but then you say "make a new workbook". Loading from a different workbook would be a bit more complicated. – Christofer Weber Nov 4 '16 at 17:24
  • The IF function will check if a condition is true or false, and can't really have multiple options. You could nest a bunch of IF statements together to check additional conditions, but that can get a bit much, especially if you need to check for 80 different conditions. If you could supply an example of the spreadsheet, it would be a bit easier to give more specific help. – Christofer Weber Nov 4 '16 at 17:42
  • You could use an If() statement, with and(or()) perhaps? =If(And(Or([level of service]=Option1,[level of service]=Option2,[level of service]=Option3,[level of service]=Option4),([length of contract]=Option1,[length of contract]=Option2, ... ? – BruceWayne Nov 4 '16 at 18:08
  • Thanks for coming back to me. Apologies for the vagueness of the question, I'm not entirely sure how I want this to come out and present itself or even which is the best way to achieve this. I appreciate you taking the time to give me some suggestions. – andrewbenson1987 Nov 7 '16 at 9:13
0

This can be done in a lot of ways depending on how your spreadsheet looks, how you are selecting the options and so on. With nothing to go on, and no replies for further clarification, I'll just give you an example on how it COULD look.
(Note that my excel options uses semicolon instead of comma, for most people, the function code in this picture should be: =VLOOKUP(B10,Table1,2)*C10*D10*(1-VLOOKUP(D10,Table3,2)))

enter image description here

Let's say this is how it looks. Nevermind the fact that they are on the same worksheet, this can be easily changed.

What I've done here is used Data Validation to create a few drop-down lists to select service, length and quantity. And then added a formula to sum up the result of the selected options.

Here follows a short guide on how to do it:

STEP 1
First create the tables with the data. They don't have to be in tables.

STEP 2
Create drop-down lists with data validation.
Select the cell which should represent the level of service and go to the data tab and select data validation. Select "list" and then add the source. enter image description here

Repeat the process for the other two selection boxes.
Note. For single column tables – like table 2 in this example – you could replace the source range with =INDIRECT("Table2")

STEP 3
Adding it all together.

This was the original question, and in this case it's simple to add them together.
The Idea is to just multiply the option field together like

=B10*C10*D10

But that obviously doesn't quite work in this case.

First of, B10 shows the name of the service, not the price. So we use =VLOOKUP() to see what the price of the service is.
Syntax for VLOOKUP is =VLOOKUP (value, table, col_index, [range_lookup])
The value to look up is in B10, the table we want to check is "Table1" and the column with the result we want is column 2 (If it isn't a table, using B3:C6 works just as well).
We replace B10 with VLOOKUP(B10,Table1,2).
Now we have =VLOOKUP(B10,Table1,2)*C10*D10 which works, but wait, we are missing something!

The discount.
I use the same method with VLOOKUP() to determine the discount of the quantity.

VLOOKUP(D10,Table3,2)

returns the value 0.02 in this case. But we can't multiply it all with 0.02, we want a 2% discount. So we add (1- before it, to invert the % and get 0.98.
Now we can add it to the function. And the result is:

=VLOOKUP(B10,Table1,2)*C10*D10*(1-VLOOKUP(D10,Table3,2))

I hope this example will help you on your way with your project. Good luck!

edit
Oh, almost forgot. To refer to a range, table or cell on another worksheet, just add the worksheet name and ! in front of it. So B2 on Sheet2 would be Sheet2!B2
Good luck!

| improve this answer | |
  • Thanks for getting back to me. I'll give it a go. Thanks for taking the time, it's much appreciated! – andrewbenson1987 Nov 7 '16 at 8:41
  • Thanks for the tutorial, gave it a try and it works really well for what I'm looking to do. I have one problem however, when I formatted the example given I can't seem to get the lookups to reference the 'standard' rates, it returns the top bracket. The formula I'm using is: =(VLOOKUP(C9, Table1,2))*(1-VLOOKUP(C11,Table3,2))*(1-VLOOKUP(C13,Table2,2)) The cell reference is C3. It picks the other values up ok, but not that one. I'm not sure how to post a screenshot into my replies to show you. Any thoughts? Thanks, – andrewbenson1987 Nov 7 '16 at 9:51
  • To attach a picture, first you need a screenshot. I'd recommend the built in snipping tool in windows. Anyway, you can edit in the original question and select the image tool or press ctrl+g to import a picture. Just drag and drop. Which value do you mean is returned wrong? And what do you mean top bracket? The first value in the list? I know it's a bit hard to explain, (or hard to understand) but a picture would surely help. – Christofer Weber Nov 7 '16 at 16:34

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.