Using IF function with Drop Down data validation to calculate prices

Question

Hi there,

I'm using Excel for Mac 2010 to create a cost estimator for my small business.

I've created a table that has the following variables:

A1 - Product A

B1 - Drop Down List - Small, Medium, Large

C1 - Numerical Amount - Shipping.

D1 - [Input Quantity]

E1 - Price

I've already done the Data Validation to create the drop down list in B1, but what I want to do is put  a formula into E1 that calculates the quantity in D1 x the option selected in B1 + the flat amount in C1 (Shipping). So if you select Small from the drop down in B1, the price would be $10, if you select Medium, the price would be $15 and if you select Large, the price would be $20. Shipping is always $10 flat. The option selected in B1 is multiplied by the quantity input into D1 and then an extra $10 is added for shipping.

Can anyone help me with the formula for this? I'm relatively fluent in Excel, but I'm no expert on IF and VLOOKUP formulas which is what I think it needed for this.

Cheers!

Amy

Answer 1

One way:

If you've only got the one set of prices, this will work fine:

=IF(B1<>"", LOOKUP(B1, {"Large","Medium","Small"}, {20,15,10}) * D1 + C1, "")

OTOH, if you've got a large number of products, I'd use a table, say:

J               K             L            M

1   Product      Small    Medium   Large

2   Product A     10         15         20

3   Product B      12        18         24

4   Product C       8         12         16

You could then do something like:

=IF(B1<>"", VLOOKUP(A1, J:M, LOOKUP(B1, {"Large","Medium","Small"}, {4,3,2}), FALSE) * D1 + C1, "")

and copy down for the remaining products. Note that the table could be on a separate sheet (and hidden if desired).

If your table headers in row 1 are the same as your choices (as shown above), then you can make this more efficient by naming the table (say, "Table" - select the range, or entire columns, and enter the name in the Name box at the left side of the Formula bar) and using something like:

=IF(B1<>"", VLOOKUP(A1, Table, MATCH(B1, OFFSET(Table,,,1,), FALSE)) * D1 + C1, "")