Beware the Nested IF Formula in Excel

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.

But, things can go horrible wrong if you try to do too much with IF — sometimes other functions do a better job.

A Simple IF Formula

I often use a simple IF formula, like the one in the screen shot below, to test for empty cells. In cell E7, 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.

__________________

6 Responses

1. Yes, those nested ifs are dangerous. Mapping table is the way to go. With letter grades watch out as adding + or – grades (B+ B-) could mess up the closest match. I stick with exact match.
I explain here:
https://youtu.be/Ttsm32p-buA
As always thanks for great content Deb
Cheers
Kevin Lehrbass

2. Anonymous says:

INDEX function is really powerful. Everyone should know it and use it as often as it is possible. Life is easier with INDEX function :)

1. September 21, 2015

[…] week I wrote about the IF function, and how it can lead to some complex and messy nested IF formulas. The IF function is invaluable, but don't abuse it, if another function could do the job […]