MrExcel Publishing
Your One Stop for Excel Tips & Solutions

if/then formulas

Posted by Liliana Avancena on December 11, 2001 1:02 PM

I am trying to calculate a value based on a range. What I want the formula in A1 to calculate is if B1 equals a number within a given range, then A1 equals a value. But I also want to give several ranges, i.e.

col B col A
0-50 = A
51-70 = B
71-90 = C etc...

Can anyone help?

Posted by Aladin Akyurek on December 11, 2001 1:19 PM

Liliana --

Supposing that I understood you correctly,

in A1 enter: =VLOOKUP(B1,{0,"A";51,"B",71,"C"},2)

which should work better than a long chain of IFs.

The {0,"A";51,"B",71,"C"} bit represents a 2-column table, which can also be entered in some range, say, E2:F4, where you put 0 in E2 and A in F2, 51 in E3 and B in F3, etc. You can expand this column at will as long as you observe the regularity for every pair of entries.

When you have such a table, you can change the VLOOKUP formula to: