More nested IF options

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

Excel alternatives to nested IF’s from our readers.

After David Goodmanson’s features on IF and nested IF’s we’ve received many messages from readers suggesting another alternative – CHOOSE() and here even more possiblities.

Tony D writes:

Small snippet for your “IF” library next time. I’m sure there will be hundreds of better ideas, but you can embed an array of choices in the formula.

=MATCH(“C”,{“A”;”B”;”C”},0) 

returns 3, for the third position

or

=IF(ISERROR(MATCH(“D”,{“A”;”B”;”C”},0)), “FALSE”, “TRUE”) 

would also work, giving False in this case

Paul H writes:

I enjoyed reading David Goodmanson’s article on Nested “Ifs and Alternatives” (OfMM#10.12), and hearterly endorse the use of LookUp Tables and VBA as an alternative.

It was surprising, however, that the AND(…) and OR(…) functions were not mentioned, as they can reduce a highly complex (and messy) nested function, as in David’s great example, to a function with only 2 levels of nesting.

In David’s example, the formula is testing to see if cell “A2” contains any of a series of letters, i.e. “A” or “B” or “C” etc. This could be written as:

=IF(OR(A2=”A”,A2=”B”,A2=”C”,…),TRUE,FALSE)

This provides a smaller function that’s earier to interpret and error-check.

The AND(…) function works in a similar way.

Thanks David for a series of interesting articles and clever alternatives.

Brian P points out a gap in our last Select Case statement which starts as:


Select Case Rating
Case 4.5 To 10
Prediction = “Boom”
Case 3.5 To 4.49
Prediction = “Recovery”

If a Rating value extends to 3 decimal places like 4.493 it won’t be caught in the above code because it’s between 4.49 and 4.5 – the Case Else will be applied.

In real life code (as opposed to examples to make a particular point) you have to check that values are to the precision needed. In this some real world solutions could be:



  • ROUND() the Rating value to the needed precision eg Select Case Round(Rating,2)

    • Or one of the rounding alternatives – ROUNDUP, ROUNDDOWN CEILING, FLOOR, MROUND, INT etc.

  • Test the data when entered and ensure it’s in the correct range and precision before being evaluated.
  • Use a more flexible alternative to SELECT CASE which can handle greater than or less than eg

IF Rating >= 4.5
  Prediction = “Boom”
ElseIf Rating  3.5
  Prediction = “Recovery”
….

Here the use of ranges and the >= ensures that no value, regardless of precision, falls through undetected.

subs profile e1563205311409 - More nested IF options
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