More Excel alternatives to nested IF statements

Office for Mere Mortals
Your beginners guide to the secrets of Microsoft Office
Invalid email address
Tips and help for Word, Excel, PowerPoint and Outlook from Microsoft Office experts.  Give it a try. You can unsubscribe at any time.  Office for Mere Mortals has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy

Nested IF statements can be hard to understand and manage but there are alternatives which can be easier to handle, fix and change.

We’ll start with the relatively simple example we used for demonstrating IF and nested IF.

=IF(C2>89,"HIGH DISTINCTION",IF(C2>79,"DISTINCTION",IF(C2>69,"CREDIT",IF(C2>49,"PASS","FAIL"))))

more excel alternatives to nested if statements microsoft office 33465 - More Excel alternatives to nested IF statements

This example has only five levels of nesting, we’ve seen nested IF’s with many, many more than that.  Those long formulas can be very hard to understand (especially if you’re new to the worksheet) and debug (one missing or extra comma is enough).

Happily, there are alternatives which return the same result but are a lot easier to understand, change and fix when they go wrong.

We’ve already mentioned IFS, the alternative to nested IF’s but only available in Excel 365 and Excel 2019.

VLookup / XLookup

This alternative has a lookup table of values and results plus a formula to lookup the result.

Start by making a table of values and results.

more excel alternatives to nested if statements microsoft excel 33467 - More Excel alternatives to nested IF statements

Then use either Vlookup() or Xlookup() to lookup marks and return a grade.

Xlookup() is the new Excel 365 function.  It’s a far better and easier option than Vlookup() or Hlookup() but only works in the most recent Excel for Windows.

Vlookup() is, for the moment, the more compatible option.

We’ll show you both choices.

Any lookup table must be sorted in ascending order so the lookup function will find the correct match.  Our lookup table is called MarknGrade.

Vlookup()

more excel alternatives to nested if statements microsoft excel 33469 - More Excel alternatives to nested IF statements

=VLOOKUP(C2,   ‘ the value to lookup with.

MarknGrade,  ‘ the table or range to lookup against.

2,  ‘ which column of the lookup table to return

TRUE   ‘ look for approximate match (True) or exact match (False)

)

Xlookup()

The newer function is much better but not yet widely available.

Xlookup() lets you choose any column for the result (not just relative to the lookup column), has better matching and searching options plus an in-built ‘no match’ result.

more excel alternatives to nested if statements microsoft excel 33470 - More Excel alternatives to nested IF statements

=XLOOKUP(C2,  ‘ the value to lookup

MarknGrade1[Mark],   ‘ the range to lookup

MarknGrade1[Grade], ‘ the range to return

“What?”,  ‘ the value to return if there’s no match

-1  ‘ the lookup type   -1  means exact match or next smaller match

)

Why lookup tables are better

Either of these options is easy to change if (when) necessary.  Instead of digging into a long nested IF formula, just change the lookup table.

Let’s say the Grade names are changed:

more excel alternatives to nested if statements microsoft excel 33471 - More Excel alternatives to nested IF statements

Or the mark ranges change with a tougher passing grade and higher scores needed for the higher honors.

more excel alternatives to nested if statements microsoft excel 33472 - More Excel alternatives to nested IF statements

The VBA option

Another options, especially for more complex situations is a custom VBA function or user-defined function UDF.

This is overkill for a simple nested IF situation but, as we’ll see, ideal for more complicated conditions.  It’s also a lot easier to read and understand.

In a macro enabled workbook’s VBA Module, make a custom function like this:

Public Function GradeName(Mark As Integer)

Select Case Mark
Case Is >= 90
    GradeName = "High Distinction"

Case Is >= 80
    GradeName = "Distinction"

Case Is >= 70
    GradeName = "Credit"

Case Is >= 50
    GradeName = "Pass"

Case Is >= 0
    GradeName = "Fail"

Case Else
    GradeName = "Incomplete"
End Select
End Function

In Excel, the custom function appears just like any other function.

more excel alternatives to nested if statements microsoft excel 33473 - More Excel alternatives to nested IF statements

So far, so easy. We’ve just copied what’s possible in other ways, however the VBA is easier to read and there’s room for comments or commented out lines of code.

more excel alternatives to nested if statements microsoft excel 33474 - More Excel alternatives to nested IF statements

What if things are more complicated?  Like a special mark adjustment by age? That can be done in Excel formulas but might be easier to understand if added to a custom function.

more excel alternatives to nested if statements microsoft excel 33475 - More Excel alternatives to nested IF statements

The possibilities are endless as long as the custom function gets the input parameters.

See Many Ordinal RANK() options in Excel with joint, equal rankings, words and more for some examples of a custom Excel function that does things not possible or difficult in formulas alone.

img 5a5e51698e5f6 - More Excel alternatives to nested IF statements

 

Latest news & secrets of Microsoft Office

Microsoft Office experts give you tips and help for Word, Excel, PowerPoint and Outlook.

Give it a try. You can unsubscribe at any time.  Office Watch has been running for over 20 years, we've never, ever revealed or sold subscriber details.  Privacy policy
Invalid email address