Nested If Statement

[DontExcelAtMuch]

Hey Everyone,

I'm having a tough time figuring out how to arrange a formula that will look off a table of various prices and return the appropriate fee.

A simple workbook is attached as an example. Nested IF.xlsx

Any help would be greatly appreciated. Thanks a lot!

[6StringJazzer]

You have to change the data in column B to match the headings in the table, so change "Base" to "Base Price". Once you have done that, put this formula in D2 and copy down:

Please Login or Register  to view this content.

[DontExcelAtMuch]

This works to display the base price or the premium price, but the premium price should be added into the base price, if the product is in fact premium. How do I go about integrating this into the formula?

As an example, a Blue, Premium should show $8.97 ($7.59+$1.38)

[hoyasaxa215]

Just add the Base Price to the Premium Price calculation.

The formula in C3 would be: =INDEX($I$3:$J$4,MATCH(A3,$H$3:$H$4,0),MATCH(B3,$I$2:$J$2,0))+C2
The formula in C5 would be: =INDEX($I$3:$J$4,MATCH(A5,$H$3:$H$4,0),MATCH(B5,$I$2:$J$2,0))+C4

[DontExcelAtMuch]

Hi hoyasaxa215. I need a formula that I can pull down for large amounts of data though. Can't rely on having different formulas in the column for different data. I need one formula for everything

[6StringJazzer]

I missed that the first time. However, what you describe is not good data design. You want one formula for everything, but your data means different things in different columns. My suggestion would be to have a column with Premium Add-On and then a new column with Premium Total. Then you would do a SUM in that column, and the label to look up would be Premium Total, and then you could use the same formula in all rows.

It's hard to give more specific advice without seeing your real data layout. I get the sense this is a stripped-down example.

[hoyasaxa215]

I concur. Break out the columns and do the calculations separately. It'll make for a much more robust model going forward.