Excel Nested IF Formula Horror Stories

Written by Debra Dalgleish from Contextures Blog

Is the Excel IF function underused, overused or too often abused? Or some combination of all three? This short video shows how to make a simple IF formula. It checks if a cell is empty, then returns a result, based on that test. Too often though, the IF function is nested about 15 layers deep. Those formulas are confusing, and could usually be replaced by a much simpler solution. What are your nested IF Formula horror stories?

Video: Simple IF Formula in Excel

This video shows the steps to create a simple IF formula in Excel.

Video Timeline

0:00 Introduction
0:13 Do a Test
0:39 Order Form
0:55 Simple IF Formula
1:39 Test the Formula
1:55 Get the Workbook

What’s in an IF Formula?

The IF function has 3 arguments:

TEST something, such as the value in a cell.
Specify what should happen if the test result is TRUE.
Specify what should happen if the test result is FALSE.

Simple IF Formula for Total Price

In the video, the IF formula, in cell E7, calculates the total price, if the Quantity cell is NOT empty.

=IF(D7=””,””,C7*D7)

These are the 3 parts (arguments) for the Total Price IF formula:

TEST cell D7, to see if it is empty (D7=””),.
If D7 is empty (TRUE), the cell with the Total formula will show nothing (“”).
If D7 is not empty (FALSE), the cell with the Total formula will multiply Price x Quantity (C7*D7)

In the screen shot below, cell D7 is NOT empty (FALSE), so the IF formula in cell E7 shows the total price.

IF Formula Horror Stories

Here’s the overly ambitious IF formula that I saw last week. Yes, there are eight IF functions nested in it – my eyes are still burning!

This is a desperate cry for a lookup table, right?

=IF(OR(ISBLANK(A2),ISBLANK(B2),ISBLANK(D2)),,
IF(D2=1,B2,
IF(D2=2,B2*1.5,
IF(D2=3,B2*2.1,
IF(D2=4,B2*3.3,
IF(D2=5,B2*5.1,
IF(D2=6,B2*6.2,
IF(D2=7,B2*8,”Unknown”))))))))

And I’ve seen workbooks with worse IF formula horror stories – how about you?

Instead of Nested IF Formulas

Instead of an 8-level nested IF formula, what would you use?

I’d make a nice little lookup table with all the multipliers. That way, they’re easy to see, and simple to change, when you want to do an update.
Then, I’d use a lookup function, like VLOOKUP, XLOOKUP or INDEX / MATCH, to get the multiplier.

For example, here’s a formula that counts the entries, then uses VLOOKUP to get the multiplier. For errors, IFERROR returns “Unknown”)

=IFERROR(IF(COUNTA(A2,B2,D2)<3,””,B2*VLOOKUP(D2,tblRank,2)),”Unknown”)

The lookup list is in this formatted Excel table, named tblRank.

Get the Workbook

To get the sample Excel workbook, go to the IF Function Examples page on my Contextures site. The zipped file is in xlsx format, and does not contain any macros.

And instead of creating long, complicated, multi-level nested IF formulas, considering using a lookup formula instead.

INDEX / MATCH

VLOOKUP

Lookup Functions Compared

——————————–