Replace 25+ Nested IFs with a Single Simple Lookup

I was recently asked the following question: “I have a drop-down with 25+ categories that determines the fee percentage. Can I use an IF function to compute the fee based on the selected category?” I answer this question in this post.

Video

Objective

Let’s begin by understanding the objective and the question.

There is an in-cell drop down that enables the user to pick a Category (from a list of 25+ categories) in C9. The price for the product is entered into C10. And we want Excel to compute the Fee in C11.

The initial question asks how to use an IF function to compute the Fee based on the Category and Price. So, with that context provided, let’s jump in.

Step-by-step

We’ll walk through this process using a couple of exercises.

Exercise 1: Conditional logic with IF, IFS, and SWITCH

Let’s start with the suggested approach of using the IF function. We would start with the first category, category “A” like this:

=IF(C9="A",C10 * 0.01)

As you can see, if the Category is A then the fee is the Price C10 times 0.01 or 1%. If the category is B, then the fee is 2%, so we could address this by nesting in an additional IF function to our formula:

=IF(C9="A",C100.01,IF(C9="B",C100.02))

Category C is computed at 3%, so we’d nest an additional IF function like this:

=IF(C9="A",C100.01,IF(C9="B",C100.02,IF(C9="C",C10*0.03)))

And we could continue nesting IF functions to address all 25+ categories like this:

=IF(C9="A",C100.01,IF(C9="B",C100.02,IF(C9="C",C100.03,IF(C9="D",C100.04,IF(C9="E",C100.05,IF(C9="F",C100.06,IF(C9="G",C100.07,IF(C9="H",C100.08,IF(C9="I",C100.09,IF(C9="J",C100.1,IF(C9="K",C100.11,IF(C9="L",C100.12,IF(C9="M",C100.13,IF(C9="N",C100.14,IF(C9="O",C100.15,IF(C9="P",C100.16,IF(C9="Q",C100.17,IF(C9="R",C100.18,IF(C9="S",C100.19,IF(C9="T",C100.2,IF(C9="U",C100.21,IF(C9="V",C100.22,IF(C9="W",C100.23,IF(C9="X",C10=0.24,IF(C9="Y",C100.25,IF(C9="Z",C10*0.26))))))))))))))))))))))))))

However, even the biggest IF fans will agree that dealing with multiple nested IF statements can become cumbersome. Not only are they challenging to write, but they’re also tough to maintain and update over time. Like, I can’t even imaging trying to update the fee percentages for each category!

There are other functions that have come out recently that can help simplify this slightly, such as IFS and SWITCH. But they are still cumbersome. For example, we could replace the above function that uses nested IF function with the following:

=IFS(C9="A",C100.01,C9="B",C100.02,C9="C",C100.03,C9="D",C100.04,C9="E",C100.05,C9="F",C100.06,C9="G",C100.07,C9="H",C100.08,C9="I",C100.09,C9="J",C100.1,C9="K",C100.11,C9="L",C100.12,C9="M",C100.13,C9="N",C100.14,C9="O",C100.15,C9="P",C100.16,C9="Q",C100.17,C9="R",C100.18,C9="S",C100.19,C9="T",C100.2,C9="U",C100.21,C9="V",C100.22,C9="W",C100.23,C9="X",C100.24,C9="Y",C100.25,C9="Z",C100.26)

As you can see, it is slightly less complex.

Or, we could use SWITCH, which also helps a bit:

=SWITCH(C9,"A",C100.01,"B",C100.02,"C",C100.03,"D",C100.04,"E",C100.05,"F",C100.06,"G",C100.07,"H",C100.08,"I",C100.09,"J",C100.1,"K",C100.11,"L",C100.12,"M",C100.13,"N",C100.14,"O",C100.15,"P",C100.16,"Q",C100.17,"R",C100.18,"S",C100.19,"T",C100.2,"U",C100.21,"V",C100.22,"W",C100.23,"X",C100.24,"Y",C100.25,"Z",C100.26)

But, there is a better option in this scenario, so let’s get to it before my brain implodes.

Exercise 2: lookup table

Rather than embedding the fee rates directly into the formula, as we did above, we can store them in cells, like this:

Then, based on the category chosen, we can retrieve the correct fee percentage from this lookup table. So, how can we do that? Well, that leads us to the next exercise.

Exercise 3: single lookup

With the fee schedule created, we can retrieve the correct rate using our favorite lookup function, whether that is VLOOKUP, XLOOKUP, INDEX/MATCH or other.

For example, we’d like to write a formula in C6 that multiplies the Price by the corresponding fee:

We can write the following formula into C6:

=XLOOKUP(C4,B11:B36,C11:C36)*C5

This multiplies the percentage returned by XLOOKUP by the price in C5:

And we got it!

In the end, we replace an intricate approach using over 25 different conditions with a single simple lookup function. And the best part is that is was easy to write AND maintain going forward. When there are rate changes, all we need to do is update the lookup table. No changes are needed to our formula 🙂

Note: For more about the XLOOKUP function, check out this post.

Conclusion

That is how we can replace 25+ nested IFs with a simple single lookup function. Mission accomplished!

If you have any enhancements, questions, or suggestion … please share by posting a comment below, thanks!

Sample File

FAQs

Q: Can I use a dropdown of 25+ categories to compute the fee with an IF function?

Yes, but it results in an unnecessarily complex setup. Using a fee schedule with a lookup function simplifies this process.

Q: Is the IFS function more efficient than nested IF statements?

Yes, it is more efficient as it reduces the need for repetitive IF functions. The SWITCH function also provides a cleaner approach than nested IFs. However, in this type of scenario, a lookup table would be the way to go.

Q: What is amore efficient method for calculating fees based on category?

The XLOOKUP function in conjunction with a lookup table is a more streamlined approach. It eliminates excessive conditional statements and simplifies formula construction by removing the fees from the formula and places them into cells which are easy to read and update.

Q: How can I convert the percentage found by XLOOKUP into a fee?

You simply need to multiply the percentage found by XLOOKUP by the price.