IF statements from a long list?

I'm a very green Excel user. I am trying to create an options invoice that will allow drop down lists for options we offer. That part I've got. But when I have longer lists (the one that I'm hung up on is ten items long), the IF statements (for the price cell) stop working. It seems they stop working after eight. Here is what I had:

=E24*IF(G24="Standard",0,(IF(G24="Kitchen Faucet #1",160,IF(G24="Kitchen Faucet #2",260,IF(G24="Kitchen Faucet #3",210,IF(G24="Kitchen Faucet #4",170,IF(G24="Kitchen Faucet #5",270,IF(G24="Kitchen Faucet #6",300,IF(G24="Kitchen Faucet #7",240,IF(G24="Kitchen Faucet #8",350,IF(G24="Kitchen Faucet #9",390,)))))))))))

Note: E24 is the quantity

Some poking around on Google indicates that nested IF statements are limited to eight. If that is true, how would I get this functionality for longer lists? Is there a better/easier method I should be using?



More background: In case it is not obvious, I want to have a column for quantity that is manually input, then a column for options that would allow selection from a pre-determined drop down list, then a column that would indicate the price based on what is selected from the drop down list and the quantity selected.

Place your pricing into a table somewhere in your workbook out of sight, on another sheet would be a good place. Name it PricingTable. In the first column, place all of your labels such as Standard, Kitchen Faucet, etc. In the second column, put all of your prices. Then use this formula

Please Login or Register  to view this content.

As per tlafferty's description:

Thanks that seems to have worked, but now the price is displayed below the item in the drop down as well. Is there any way to eliminate that?

ETA: I just saw ConneXionLost's response. Since his example doesn't do that, I must have done something wrong. I'll poke around more in that example to see what is different. Thanks!

ConneXionLost - Slick! I've wondered sometimes how to make a table dynamic via formula so users don't have to be aware of how to expand the range - sweet!

Hi there - here's how VLOOKUP works:
the first argument after the opening parenthesis ( -in this case G24- is the value you want to lookup or find a match for in your pricing table. So we're entering G24 in the formula, which instructs Excel to go searching for whatever value you entered into that cell.

The second argument in the formula is the range to search through to find a match. VLOOKUP always looks vertically through the first column in a range you tell it to examine, in this case the first column of the KitchenSpray where you should have entered your fawcet makes and models.

The third argument tells it what column to pull from, in this case column 2. Your named range KitchenSpray needs to be 2 columns wide, not 1.

The last argument instructs Excel what kind of match to use (FALSE = exact, TRUE = close).

To avoid seeing the prices, name the first column of your KitchenSpray table something like Options, and use the formula

Please Login or Register  to view this content.

in your data validation.

Another way, but not so flexible as a lookup table:

=E24*( (G24="Standard")*0 +(G24="Kitchen Faucet #1")*160+(G24="Kitchen Faucet #2")*260+(G24="Kitchen Faucet #3")*210+(G24="Kitchen Faucet #4")*170+(G24="Kitchen Faucet #5")*270+(G24="Kitchen Faucet #6")*300+(G24="Kitchen Faucet #7")*240+(G24="Kitchen Faucet #8")*350+(G24="Kitchen Faucet #9")*390)


Regards