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.

For example, in the screen shot below, the total won’t show if a quantity hasn’t been entered in cell D7.

iffunction01

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.

iffunction08

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”.

iffunction09

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.

iffunction10

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.

iffunction11

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!

iffunction12

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.

Beware the Nested IF in Excel http://blog.contextures.com/

__________________