Do you use the IF function in your workbooks? It’s helpful if you want to check something, and then show a result based on the result of your test.
For example, in the screen shot below, the total won’t show if a quantity hasn’t been entered in cell D7.
Nested IF Formulas Gone Wrong
While I was updating the IF Function page on my website this week, I remembered some of the overly complex IF formulas that I’ve seen.
For example, do you ever see nested IF formulas that look like this? Not in your workbooks, of course, but your co-workers might create them!
=IF(B4>=85,”A”,IF(B4>=70,”B”,
IF(B4>=60,”C”,IF(B4>=50,”D”,”F”))))
That formula was designed to convert students’ numeric scores into letter grades.
Create a Lookup Table
Sure, that long, nested IF formula works, but it’s difficult to read, and a pain to maintain. If the scoring system changes, you’ll have to find all those IF formulas and change them.
Instead of building nested IF formulas to do this type of “lookup”, use the INDEX and MATCH functions, or the VLOOKUP function instead.
Create a list that shows the values, like the numeric and letter grades in the screen shot below. The range A2:B6 is named “GradeList”.
Use a VLOOKUP Formula
Then, you could get the letter grades with a simple VLOOKUP formula:
=VLOOKUP(B4,GradeList,2,TRUE)
With VLOOKUP, the lookup values have to be in the leftmost column of the lookup range.
Use an INDEX and MATCH Formula
Or, if you prefer, you could use an INDEX and MATCH formula:
=INDEX(Grades!$B$2:$B$6,
MATCH(B4,Grades!$A$2:$A$6,1))
With INDEX and MATCH, the lookup values can be in any column of the lookup table.
Easy to Maintain
With a lookup table, it’s easy to change the scoring system, and maintain the Excel file.
Just make the updates in the table, and all the VLOOKUP or INDEX and MATCH formulas will automatically show the correct data.
You can also use a Named Excel Table, to make the maintenance even easier. It will automatically adjust if you add or remove rows or columns.
And it’s much easier than trying to add more nested IFs, to include all the new plus (+) grades!
Download the IF Function
You can download the sample file for the IF function, from my Contextures website. It was updated this week, to include all the new samples from the IF Function page. The zipped file is in xlsx format, and does not contain macros.
__________________