# Excel formulas with multiple conditions to reference a separate worksheet

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

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))`) 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. 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

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))
``````

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`