MrExcel Publishing
Your One Stop for Excel Tips & Solutions

IF/THEN help


Posted by Mike on October 19, 2001 10:29 AM

I need help figuring this formula out.

If cell A is 90% - 97% then Cell B should populate 100%If cell A is 97.1% - 99.9% then Cell B should populate 125%
If cell A is 100% then Cell B should populate 150%


Posted by Mark W. on October 19, 2001 10:39 AM

=VLOOKUP(A1,{0.9,1;0.971,1.25;1,1.5},2)

Posted by Don C on October 19, 2001 10:42 AM

Try a lookup table

You could use some IF statements, but I'd just make a little lookup table with the upper values (97.0,97.9,100 in column D and 100, 125, and 150 in column E). Place the table on an unused section of the worksheet or on a separate worksheet and refer to it with VLOOKUP in your column B.

Posted by Don C on October 19, 2001 10:43 AM

Well, I learned something today! Did not know that you could "insert" a lookup table within the VLOOKUP function.

Posted by Mark W. on October 19, 2001 10:47 AM

Yep, in fact....

You could also assign ={0.9,1;0.971,1.25;1,1.5} to
a defined name (e.g., Lookup_Table) and use...

=VLOOKUP(A1,Lookup_Table,2)

This allows you to easily change the table
values in one place and know that all formulas
have been revised. This is a good configuation
managment (CM) practice.