How to simplify this formula?

**lorne17** · 09-24-2012, 05:06 PM

Hello there,

Is there a way to simplify this If Statement forumla?

Basically I have a dropdown list that selects form the range: C21-C34 then in E21-E34 I have prices that change per line item. This If Statement is working, but it's long and cumbersome. Plus if I ever need to add any line items it won't work. Is there a way to use sumproduct or something else that will look at the dropdown list in C39 and pull from the appropriate match in C21-C34 and list the price in Column E in same row?

=IF(C39=C21,E21,IF(C39=C22,E22,IF(C39=C23,E23,IF(C39=C24,E24,IF(C39=C25,E25,IF(C39=C26,E26,IF(C39=C27,E27,IF(C39=C28,E28))))

I have posted my example as well.

Thanks,
Lorne

**martindwilson** · 09-24-2012, 05:11 PM

use
=VLOOKUP(C39,C21:E34,3,FALSE)

**Ace_XL** · 09-24-2012, 05:14 PM

=index($e$21:$e$34,match(c39,$c$21:$c$34,0))

**lorne17** · 09-24-2012, 05:23 PM

Thanks for the quick reply guys...the VLOOKUP worked great. Now next question.

I want to calculate the cost from another table, with two if conditions. If F39 is <25 and G39=1 then it should equal C4. Then if F39 is 25-49 and G39=2 then it should equal D5. I've updated the sample on the attachements.

Thanks,
Lorne