How to avoid using nasty nested IF statements in Excel

Some people insist on doing things the hard way - such as using nested IF statements in Excel to handle multi-condition behaviours.

Quite simply - you hardly EVER have to use nested IF constructs...

I went Googling to try and find a really good case of where a nested IF was essential.

CASE 1

My first Googling found...

http://www.cpearson.com/excel/nested.htm

It presents the case of...

Suppose we wanted an nested IF formula to test:
IF A4 = 1 Then 11
Else If   A4 = 2 Then 22
Else If   A4 = 3 Then 33
Else If   A4 = 4 Then 44
...
Else If   A4 = 13 Then 130  Else "Not Found"

The author then creates a massively complicated IF solution. This is an example of where you just would not use nested IF at all.
You would use VLOOKUP.  Since the lookup values are simply 1 to 13, just use:

Name the above range "table" and use =VLOOKUP(A4,table,2)

Far better than twisting yourself up with IF...

Another strategy

The CHOOSE() function also lets you easily look up values for an index value e.g.

=CHOOSE(daynumber,"Sunday","Monday","Tuesday"... etc)

That may be useful too!

CASE 2

My second Google led to...
http://spreadsheets.about.com/cs/excelfunction1/a/nestediffunct.htm

It says...

To illustrate, let's say you have a spreadsheet that you use to keep track of your sales force. The rate of commission each sales person receives is based on the amount of sales they have generated for that month. For example:

From $1 to $10 earns 10% commission
From $11 to $100 earns 15% commission
Anything over $100 earns 20% commission

Assuming the amount of sales is in column B, starting at row 4, and that the column containing the commission is formated for percentages, this is what the nested IF function would look like:

=IF(B4<=10,"10", if(b4<=100, "15", "20"))

Once again, a simple VLOOKUP is far superior.

Name the range (excluding headings) "table" and then use
=VLOOKUP(sales,table,3)

(Assuming the value to look up is in a cell named "sales")

NOTE

Note the method I now use for setting out lookup tables.  I used to get mental cramps working out the cutoff values for categories.  Now I use "From" and "To" columns to make it much easier to determine the numbers.
The second column is actually never used by the lookup, but it helps my brain.  Life is so much more pleasant now  :-)

CASE 3

Example 3, found at
http://www.techonthenet.com/excel/formulas/if_nested.htm

Question:  In Excel, I need to write a formula that works this way:

> If (cell A1) is less than 20, then times it by 1,
> If it is greater than or equal to 20 but less than 50, then times it by 2
> If it is greater than or equal to 50 and less than 100, then times it by 3
> And if it is great or equal to than 100, then times it by 4

> Answer:  You can write a nested IF statement to handle this.  For example:
>  =IF(A1<20, A1*1, IF(A1<50, A1*2, IF(A1<100, A1*3, A1*4)))

Once again, it's far easier to use ...

Name the range "table" (or whatever)

In the target cell just put

=A1 * VLOOKUP(A1,table,3)

CASE 4

Example 4, found at
http://www.techonthenet.com/excel/macros/if_custom.htm

> Question:  I have a formula in Excel that I am using to test for 7
> conditions, and each condition if true will return a different value.
> However, I now need to test a total of 12 possible values.  The
> limitation of the nested IFs is that you can only nest up to 7.  Is
> there an alternative to this formula to test so that I can test for 12
> values instead of 7?

> =IF(A1="10X12",120,IF(A1="8x8",64,IF(A1="6x6",36,IF(A1="8x10",80,IF(A1="14x16",224,IF(A1="9x9",81,IF(A1="4x3",12)))))))

> Answer:  There is no built-in alternative formula in Excel, but you
> could write your own function in VBA and then call this new function
> instead.

Geez!  You could (again) use vlookup...

 
Sort by the first column so they're sorted alphabetically (or see the tip below).  Call it "table" and use =VLOOKUP(a1,table,2)

TIP

If searching a table for an exact match (e.g. cases 1 and 4) rather than for a value fitting into a continuous range (e.g. values from 26 to 73) you can use the mysterious optional parameter in vlookup...

=VLOOKUP(a1,table,2, FALSE)

By adding the " ,FALSE", vlookup will look for an exact match for the lookup value in the table. 
Note!  It gives an error if the exact value is not found.
Using the extra ",FALSE" means you don't have to sort the lookup rows into ascending order..

In short: I (nearly) never use nested IFs because usually there are easier and smarter alternatives.

Here's a challenge: come up with a neat example where it is essential to use nested IFs!  Tell me about it!

CHALLENGE 1

A worthy challenge from yangontha on 28 October 2006. If memory serves me correctly, yangontha said:

Column A has a property type and the value ranges from 1 to 5.
Column B and C has some decimal values.
In each row, depending on the value of type, the different formula is used to calculate for column D.
For example, if the type is 1, then the values in column B and C for that row are added and put in column D.
If the type is 2, then the values in column B and C for that row are multiplied and put in column D.
Depending on the value of type, there is a different formula.
The problem is how to put the formula in column D so that the respective formulae is used to calculate for column D

I am not saying, nested if is necessary. But I can't think of a way, how to put the formula in column D.

This is what I replied to yangontha. Allez Excel!

Hi Yangontha.  Not even a nested IF is required using the sample data you provided.

There are only 2 operators to cope with (multiply and add) so a simple IF does the trick.

Mind you, if you added divide and subtract to get four conditions rather than two, a different approach would be needed - but not VLOOKUP in this case - you'd use the CHOOSE function (as mentioned above).

It's not VLOOKUP, perhaps , but I never said VLOOKUP was the answer for everything. You always use the best-fitting solution to a problem: you don't force the one tool you know into solving every problem.

Good try , but nested IF is still a beaten foe. :-)

CHALLENGE 2

'Curious' wrote on Saturday, December 30, 2006 at 09:51:40

Hi Mark - What about if you have a range of dates formatted in date form (i.e. 12/29/2006) and you want Excel to search that range of data and return the month in text form (i.e. DEC). Isn't it necessary to use a nested IF for this case? I tried using a vlookup formula (quite similar to the first example listed on your website) and making a table (named MONTHS) that defines each month (ex. 1 = Jan, 2 = Feb, and so on)... naming that table and then using the following vlookup formula: =VLOOKUP(A9,MONTHS,2)
I had no luck with this and am convinced that in this instance, a nested IF formula is needed. What do you think?
Much appreciated,
Curious

And I replied:

Hi curious. You were SO close! The problem you had was that you had not extracted the month from the date to use in the lookup. VLOOKUP will do the job very nicely, with the addition of one simple MONTH( ) function    :-)

Download the Excel solution

The formulas:

CHALLENGE 3

On 15 August 2007, Jay wrote to say:

Here's 1, I am having trouble coming up with ANY way of solving:
I have 6 columns of data. I want to be able to set up a 7th column that tells me whether I have passed or failed my test criteria (by saying PASS or FAIL). the failing criteria is if 2 or more of the 6 columns display a value less than 85.
The nesting IF function can only nest 7, with 15 possible combinations in this example, so thats not even an option.
Thanks!

Hi Jay. What about this:

=IF(COUNTIF(A3:F3,"<85")>=2,"Fail","Pass")

It simply counts the number of cells that are less than 85. If there are 2 or more, they fail.

CHALLENGE 4

24 September 2007

Hi,
   
Ok here we go.....
I have created a MS EXCEL 2007 Workbook that got two sheets. I  have attached picture of both sheets.
 
in "Sheet 1" there is three Field
 
1. ID:- This is to track the customer ID=1 Means its "Customer 1"....
2. Bill No:-This is to track Month Bill 1 Means Month=January...
3.Total Bill:- This is where the problem occurs.

In sheet two there is a MATRIX TABLE...What i need to do is Bring the proper value from sheet2. Like---- if ID=1 and Bill No=1 then it will show value from B4 in sheet2.

Everything is ok . i have the code which is working but within the limit. It only accept 64 Argument or nested IF else.
But my requiremnt is more than that. The Code is Something like that
=IF(AND(N13=1,N14=1),Readings!B4,IF(AND(N13=1,N14=),Readings!C4,.................

I heard that there is a way if i make a FUNCTION and call it . But i don't now how. if u can help...
Thankx

And the result is once again far easier than expected, so long as you know what Excel has in its toolbox for you to play with...
Assuming:
- the customer ID cell is named 'customer' and
- the month number cell is named 'month' and
- the table (excluding the heading row and column) is named 'table'...

the fomula for the total bill is simply:
=INDEX(table,customer,month)
Easy as that. You can download the solution if you like.

CHALLENGE 5

11 October 2007

Sara wrote to say...

This was an interesting challenge, but solvable with a bit of lateral thinking and a nested LOOKUP.

Entering an arbitrary "type" (A-F in this case) into C2 and a "days ago" into C2 gives the value matching that combination of days and type.
The trick is to use a VLOOKUP with a HLOOKUP (its less famous cousin) inside it.

The formula in F12 is =VLOOKUP(C2,E6:F10,2) + 1

This handles the first step of the two-part lookup - it looks up the "days ago" value in the table and returns a row number (that's the tricky bit).
Then, the formula for the answer is =HLOOKUP(C1,G5:L10,F12)

This looks up the "type" in a HLOOKUP and uses the previous VLOOKUP's value to act as the HLOOKUP's row parameter.

Download the solution here.

Challenge 6

I just found this one lurking in the bottom of my hard disk. I must have lost it or forgotten to upload it. Anyway, it's an interesting one.

Someone wanted to know how to work out salaries that depended on not one but two variables: length of service and education level without the use of tortuous nested IFs. In effect, they wanted to get this...

To solve this required a bit of lateral thinking - instead of a nested IF, we need a nested lookup!

First, we do a normal lookup of years of service to find the right row in the salary table. So James, for example, with 6 years of service is looked up in the B5:E9 table and we find he's in row 2 of the table.

The hard bit is to then determine which column we need to read from to get his salary; it depends on the degree he holds. This is where the second lookup happens.

The "column" parameter of the first lookup will not be a constant - it will be dynamically calculated by another lookup. The second lookup looks up the person's degree in a second table (G6:H8). It returns a number from 2 to 4, which is used to select a column value for the first lookup.

The result is something like this:

Note the extra fourth parameter in the nested lookup - it allows the degrees to be in any order rather than sorted alphabetically.

Download the Excel source.

CHALLENGE 7

Loti wrote in 2008...

I am an HR Officer responsible for recruiting employees for a Mining company that has a workforce of 1800 employees. I maintain an employee data base in excel. I want to create a column in the database, where I will be able to know if an employee is due for the first probation assessment after 3 months.

If, after being assessed, the employee is not ready for confirmation I would like to indicate in the next column that "Emp. is Not Ready" and after another 3 months the employee should be subjected to a 2nd assessment.

If after the first assessment, the employee is deemed suitable for the position, I should indicated 'Confirmed" in the next column to close the chapter on that particular employee.

What functions can I use to know (1) when an employee is due for the first assessment, (2) if employee is not subjected to the first assessment after 3 months that the 1st assessment is overdue, and (3) when the 2nd assessment is due. I have attached part of the database for you to fully understand my problem. Regards Loti

The sample spreadsheet attached had some quite lengthy nested IF statements such as...

=IF((DATEDIF(C4,TODAY(),"d"))>80,IF(((DATEDIF(C4,TODAY(),"d")))<91,"Due",IF(((DATEDIF(C4,TODAY(),"d")))<180,IF(ISBLANK(E4),"Over due",""),"")))

It took a bit of fiddling and head scratching to work out the logic of the problem. It was interesting because there were several logical tests to consider:

How long has the person been employed?

Have they been confirmed yet?

Have they been assessed yet?

Again, I thought, VLOOKUP and his magical pony can save the day. With a relatively simple IF, a VLOOKUP and a super-sneaky IF inside the VLOOKUP we can test for multiple conditions in one sweet package...

Column A is Date of Employment. Note that in this test data I fixed "today" to a particular date so the result would not vary over time. One would use the TODAY( ) function for the real thing.

The important column is B - status. If they have not been employed for 90 days, they are not due for assessment.

If they have been employed 90 to 179 days, and they have not been assessed, their status is "due for first assessment". If they had been assessed but not confirmed, they would be shown as "Not ready 1". You can see how complex this is getting. Nested IF was simply not up to the task. This is how I did it...

Ignore the fact that DOE entries are manufactured rather than being literal dates. I did this to easily create test data for each possible DOE.

Look at column B.

If the person has NOT already been confirmed...

A VLOOKUP begins, looking up the person's length of service. Once it finds the right row in the table, it needs to choose either column 3 (if they have been assessed) or column 4 (if they have not been assessed). The respective cells contain appropriate status information. The choice of column is made in the VLOOKUP formula by the IF statement checking to see if an assessment has been made.

If the person HAS been confirmed, it's a simple status of "confirmed".

So I hope you can see how once again VLOOKUP can be made to respond to second-level decisions by using different columns for the function's return value. It's similar to a couple of the challenges above.

Thanks for the challenge, Loti!

Download the Excel solution (Excel 2007 format)

Next challenger please...