Excel Nested IF statement: examples, best practices and alternatives

Excel nested IF statement

Here's the classic Excel nested IF formula in a generic form:

You can see that each subsequent IF function is embedded into the value_if_false argument of the previous function. Each IF function is enclosed in its own set of parentheses, but all the closing parentheses are at the end of the formula.

Our generic nested IF formula evaluates 3 conditions, and returns 4 different results (result 4 is returned if none of the conditions is TRUE). Translated into a human language, this nested IF statement tells Excel to do the following:

As an example, let's find out commissions for a number of sellers based on the amount of sales they've made:

In math, changing the order of addends does not change the sum. In Excel, changing the order of IF functions changes the result. Why? Because a nested IF formula returns a value corresponding to the first TRUE condition. Therefore, in your nested IF statements, it's very important to arrange the conditions in the right direction - high to low or low to high, depending on your formula's logic. In our case, we check the "highest" condition first, then the "second highest", and so on:

=IF(B2>150, 10%, IF(B2>=101, 7%, IF(B2>=51, 5%, IF(B2>=1, 3%, ""))))

If we placed the conditions in the reverse order, from the bottom up, the results would be all wrong because our formula would stop after the first logical test (B2>=1) for any value greater than 1. Let's say, we have $100 in sales - it is greater than 1, so the formula would not check other conditions and return 3% as the result.

If you'd rather arrange the conditions from low to high, then use the "less than" operator and evaluate the "lowest" condition first, then the "second lowest", and so on:

=IF($B2<1, 0%, IF($B2<51, 3%, IF($B2<101, 5%, IF($B2<=150, 7%, 10%))))

As you see, it takes quite a lot of thought to build the logic of a nested IF statement correctly all the way to the end. And although Microsoft Excel allows nesting up to 64 IF functions in one formula, it is not something you'd really want to do in your worksheets. So, if you (or someone else) are gazing at your Excel nested IF formula trying to figure out what it actually does, it's time to reconsider your strategy and probably choose another tool in your arsenal.

For more information, please see Excel nested IF statement.

Nested IF with OR/AND conditions

In case you need to evaluate a few sets of different conditions, you can express those conditions using OR as well as AND function, nest the functions inside IF statements, and then nest the IF statements into each other.

VLOOKUP instead of nested IF in Excel

When you are dealing with "scales", i.e. continuous ranges of numerical values that together cover the entire range, in most cases you can use the VLOOKUP function instead of nested IFs.

For starters, make a reference table like shown in the screenshot below. And then, build a Vlookup formula with approximate match, i.e. with the range_lookup argument set to TRUE.

Assuming the lookup value is in B2 and the reference table is F2:G5, the formula goes as follows:

=VLOOKUP(B2,$F$2:$G$5,2,TRUE)

Please notice that we fix the table_array with absolute references ($F$2:$G$5) for the formula to copy correctly to other cells:

By setting the last argument of your Vlookup formula to TRUE, you tell Excel to search for the closest match - if an exact match is not found, return the next largest value that is smaller than the lookup value. As the result, your formula will match not only the exact values in the lookup table, but also any values that fall in between.

For example, the lookup value in B3 is $95. This number does not exist in the lookup table, and Vlookup with exact match would return an #N/A error in this case. But Vlookup with approximate match continues searching until it finds the nearest value that is less than the lookup value (which is $50 in our example) and returns a value from the second column in the same row (which is 5%).

But what if the lookup value is less than the smallest number in the lookup table or the lookup cell is empty? In this case, a Vlookup formula will return the #N/A error. If it's not what you actually want, nest VLOOKUP inside IFERROR and supply the value to output when the lookup value is not found. For example:

=IFERROR(VLOOKUP(B2, $F$2:$G$5, 2, TRUE), "Outside range")

For more information, please see Exact match VLOOKUP vs. approximate match VLOOKUP.

IFS statement as alternative to nested IF function

In Excel 2016 and later versions, Microsoft introduced a special function to evaluate multiple conditions - the IFS function.

An IFS formula can handle up to 127 logical_test/value_if_true pairs, and the first logical test that evaluates to TRUE "wins":

In accordance with the above syntax, our nested IF formula can be reconstructed in this way:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%)

Please pay attention that the IFS function returns the #N/A error if none of the specified conditions is met. To avoid this, you can add one more logical_test/value_if_true to the end of your formula that will return 0 or empty string ("") or whatever value you want if none of the previous logical tests is TRUE:

=IFS(B2>150, 10%, B2>=101, 7%, B2>=51, 5%, B2>0, 3%, TRUE, "")

As the result, our formula will return an empty string (blank cell) instead of the #N/A error if a corresponding cell in column B is empty or contains text or negative number.

For more information, please see Excel IFS function instead of nested IF.

CHOOSE instead of nested IF formula in Excel

Another way to test multiple conditions within a single formula in Excel is using the CHOOSE function, which is designed to return a value from the list based on a position of that value.

Applied to our sample dataset, the formula takes the following shape:

=CHOOSE((B2>=1) + (B2>=51) + (B2>=101) + (B2>150), 3%, 5%, 7%, 10%)

In the first argument (index_num), you evaluate all the conditions and add up the results. Given that TRUE equates to 1 and FALSE to 0, this way you calculate the position of the value to return.

For example, the value in B2 is $150. For this value, the first 3 conditions are TRUE and the last one (B2 > 150) is FALSE. So, index_num equals to 3, meaning the 3rd value is returned, which is 7%.

SWITCH function as a concise form of nested IF in Excel

In situations when you are dealing with a fixed set of predefined values, not scales, the SWITCH function can be a compact alternative to complex nested IF statements:

The SWITCH function evaluates expression against a list of values and returns the result corresponding to the first found match.

In case, you'd like to calculate the commission based on the following grades, rather than sales amounts, you could use this compact version of nested IF formula in Excel:

=SWITCH(C2, "A", 10%, "B", 7%, "C", 5%, "D", 3%, "")

Or, you can make a reference table like shown in the screenshot below and use cell references instead of hardcoded values:

=SWITCH(C2, $F$2, $G$2, $F$3, $G$3, $F$4, $G$4, $F$5, $G$5, "")

Please notice that we lock all references except the first one with the $ sign to prevent them from changing when copying the formula to other cells:

For more information, please see SWITCH function - the compact form of nested IF statement.

Concatenating multiple IF functions in Excel

As mentioned in the previous example, the SWITCH function was introduced only in Excel 2016. To handle similar tasks in older Excel versions, you can combine two or more IF statements by using the Concatenate operator (&) or the CONCATENATE function.

For example:

=(IF(C2="a", 10%, "") & IF(C2="b", 7%, "") & IF(C2="c", 5%, "") & IF(C2="d", 3%, ""))*1

Or

=CONCATENATE(IF(C2="a", 10%, ""), IF(C2="b", 7%, ""), IF(C2="c", 5%, "") & IF(C2="d", 3%, ""))*1

As you may have noticed, we multiply the result by 1 in both formulas. It is done to convert a string returned by the Concatenate formula to a number. If your expected output is text, then the multiplication operation is not needed.

For more information, please see CONCATENATE function in Excel.

Practice workbook for download

Excel nested If statement - examples (.xlsx file)