With the lookup table set up in range E4:F9 we could use this VLOOKUP function. And unless your trying to impress someone with big complex looking functions, there are better ways. Take the example below where we have a list of exam scores and we need to assign a grade for each score.Ī nested IF formulas approach could look like this. In this nested if formulas alternative, we use VLOOKUP again, but this time for a range lookup. There is no need for someone to have to edit the formula, which you or your colleagues may not be comfortable doing 10 months from now.
If a membership price changes, you can just change the lookup table. This is very compact and easier to adapt in the future. In this example I will use VLOOKUP.īy creating a lookup table (F4:G7 below) we are able to use the following VLOOKUP function to search for each membership and return the correct price. Now you can use any lookup formula that you like to achieve this. I believe an even better alternative to the IFS function for this scenario would be a lookup.
The IFS function tests cell B2 for each membership type and applies the correct price.
There are four types of membership (platinum, gold, silver and bronze). In the example below we used an IFS function to calculate the price for each membership type. You can avoid all of those brackets that come with opening and closing multiple IF functions. This function was introduced to condense and simplify the task of writing nested IF formulas. Lets start with a function that is new from Excel 2016 called the IFS function.