CHOOSE & MATCH instead of Nested IF

CHOOSE & MATCH instead of Nested IF

We all know how NESTED IF formulas in Excel can be really nasty….

Consider the following problem:

In Columns A & B, you have a list of students and (numerical) scores:

Picture 1: List of students and scores

and you want to “translate” these scores into descriptive categories, as displayed in the following table:

Picture 2: Categories Table

The solution with NESTED IF would be something like this:

Picture 3: Nested IF Solution (less recommended…)

The scores are in Column B.

The categories are in column E.

The Lower Limits for each category are in column G.

So the formula (in cell C2) would be:

Picture 4: Nested IF Formula

Now, suppose that you want to add some new categories.

The more “TRUE situations” you have, the more complicated the formula gets, which makes it quite difficult to handle. 

Fortunately, there are some better, simpler solutions.

For example:

Consider the following formula, which combines 2 functions in Excel: CHOOSE and MATCH.

Picture 5: The CHOOSE & MATCH Solution

The formula is: 

Picture 6: The CHOOSE & MATCH Formula

Explanation:

The MATCH function searches the score in B2 (150) [the first argument] within the range G2:G5 [the second argument]. The Matching Type [=1,  the third argument], tells Excel to find the largest value (within the specified range) that is less than or equal to the value searched (150). So, the greatest result it finds is the value 101 in the 3^rd cell of the lookup range, i.e: G4)

The CHOOSE function then returns the value of the 3^rd cell in the series: E2, E3, E4, E5.

The 3^rd cell is E4 whose value is GOOD.

So, the score 150 falls under the GOOD category.

Simple, isn’t it?