And that the month October is in cell F3 and the name Sancho is in E3 SUMPRODUCT((B2:B9E3)(A2:A9F3)(C2:C9LARGE(C2:C9,1,2,3))(C2:C9)) Now there are alternative methods to this, especially because you want all months and names.

This blog post explores 4 alternatives which are easier, faster and cleaner than the classic nested IF.

This function was introduced to condense and simplify the task of writing nested IFs.

You can avoid all of those brackets that come with opening and closing multiple IF functions.

In the example below we used an IFS function to calculate the price for each membership type.

There are four types of membership (platinum, gold, silver and bronze).

The IFS function tests cell B2 for each membership type and applies the correct price.

By 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.

If a membership price changes, you can just change the lookup table.

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.

Take the example below where we have a list of exam scores and we need to assign a grade for each score.

And unless your trying to impress someone with big complex looking functions, there are better ways.

With the lookup table set up in range E4:F9 we could use this VLOOKUP function.

Many Excel users will never have even heard of the CHOOSE function.

This function will perform an action based on a specific index number.

So in this example I have an interactive chart being driven from a combo box control.

Typically some of this data would be hidden or stored on separate sheets.

It is all on one sheet here to get a better idea of how it works.

A selection from the 4 leagues in the combo box will produce index number 1, 2, 3 or 4 in cell A9.

Formulas are in cells D9 and range D11:E15 returning the correct data for the chart from the combo box selection.

Now in the cells mentioned above we could use a nested IF like below.

This is the nested IF from cell D11 which returns the name of that leagues top goal scorer.

It checks the index value in cell A9 and then returns the relevant information from its list.

Excel For Mac Missing Ifs Formula _Xlfn