The IF function is great for creating a logic statement. But IF is an either/or argument, what if you need more alternatives? Combining several IF statements in a nested structure can help overcome this.
Syntax
=IF(first logical test, do something if first test is true, IF(second logical test, do something if second test is true, IF(third logical test, do something if third test is true, ….)))
Steps
- Type =IF(
- Type first condition to be checked F5<36,
- Type the value or select a cell reference if applicable that you want to return when first condition is met "XS",
- Type IF( to enter your second level condition-value pair
- Type second condition to be checked F5<39,
- Type the value or select a cell reference if applicable that you want to return when second condition is met "S",
- Continue to enter condition-value pairs until last one left
- Type the value or select a cell reference if applicable that you want to return when none of the conditions is met "Not Found"
- Type ) as many IF function as and press Enter to complete formula (6 for our example) ))))))
Note: There is a limit that how much nested IF function can be used. This limit is 7 for 2003 and previous versions and 64 for 2007 and newer versions.
How
The IF function returns one of its second or third arguments value according to result of logical statement checked at its first argument. If you want to perform more than one logical test, all you need to do is to add a new IF function with a new condition and result pair into previous IF function's third argument. This type of use of IF functions called nested IFs.
Because of writing and reading of nested IFs may become harder as you add new conditions; using line breaks (as we used in our example) may help you. To add a line break into a formula, press Alt+Enter instead of regular Enter key which works on text editor.
=IF(F5<36,"XS",
IF(F5<39,"S",
IF(F5<42,"M",
IF(F5<46,"L",
IF(F5<50,"XL",
IF(F5<54,"XXL",
"Not Found"))))))
