I need to use a lookup function in an if formula.

E

Excluxe

Creating a cost estimate sheet. I have five parameters and each one has 16
parameters. I can use lookup
=LOOKUP(A3,{1,4,5,6,7,8,10,12,14,16,18,20,22,24,30,36},{1.08,1.23,1.45,1.62,2,2.46,2.85,4.24,4.7,5.25,5.8,6.3,9.55,11.45"})
for one but this formula has five versions each with different pricing.
therefore I am trying to make an if formula something like
=IF(A2=1,"=LOOKUP(A3,{1,4,5,6,7,8,10,12,14,16,18,20,22,24,30,36},{1.08,1.23,1.45,1.62,2,2.46,2.85,4.24,4.7,5.25,5.8,6.3,9.55,11.45})",IF(A2=2,"B",
IF(A2=3,"C",IF(A2=4,"D","F")))) Problem though is it takes the answer as text
and not another formula ideas solutions or am overloading the program.
 
P

Peo Sjoblom

=IF(A2=1,LOOKUP(A3,{1,4,5,6,7,8,10,12,14,16,18,20,22,24,30,36},{1.08,1.23,1.45,1.62,2,2.46,2.85,4.24,4.7,5.25,5.8,6.3,9.55,11.45}),IF(A2=2,"B",
IF(A2=3,"C",IF(A2=4,"D","F"))))

I assume you will replace B, C etc with other versions of the formula, make
sure you don't have quotations like "lookup(etc)"

Another way would be to use


=CHOOSE(A2,LOOKUP(A3,{1,4,5,6,7,8,10,12,14,16,18,20,22,24,30,36},{1.08,1.23,1.45,1.62,2,2.46,2.85,4.24,4.7,5.25,5.8,6.3,9.55,11.45}),"B","C","D")

and of course replace the same way if necessary, it's a bit shorter
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top