• Skip to main content
  • Skip to primary sidebar
  • Skip to footer

Microsoft Excel Tips and Tricks - Computergaga

  • Home
  • Excel Online Courses
  • Excel Functions List
  • Free Downloads
  • Blog
  • Tutorials
  • About
Home / Formulas / 4 Alternatives to Nested IF Formulas

4 Alternatives to Nested IF Formulas

Nested IF formulas are extremely useful for complex decision making on a spreadsheet, but they can also be long, messy and convoluted.

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

Watch the Video – Nested If Formula alternatives

1. The IFS Function (Excel 2016 + only)

Lets start with a function that is new from Excel 2016 called the IFS function.

This function was introduced to condense and simplify the task of writing nested IF formulas. 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.

=IFS(B2="Platinum",110,B2="Gold",90,B2="Silver",65,B2="Bronze",40)
IFS function as a nested if formulas alternative

2. Using VLOOKUP for an Exact Match

I believe an even better alternative to the IFS function for this scenario would be a lookup.

Now you can use any lookup formula that you like to achieve this. In this example I will use VLOOKUP.

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.

=VLOOKUP(B2,$F$4:$G$7,2,FALSE)
VLOOKUP for an exact match

This is very compact and easier to adapt in the future. 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.

3. Using VLOOKUP for a Range Lookup

In this nested if formulas alternative, we use VLOOKUP again, but this time for a range lookup.

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

VLOOKUP for a range lookup

A nested IF formulas approach could look like this.

=IF(A2>=90%,"A",IF(A2>=80%,"B",IF(A2>=70%,"C",IF(A2>=60%,"D",IF(A2>=50%,"E","F")))))

Or even like this.

=IF(AND(A2>=90%,A2<=100%),"A",IF(AND(A2>=80%,A2<90%),"B",IF(AND(A2>=70%,A2<80%),"C",IF(AND(A2>=60%,A2<70%),"D",IF(AND(A2>=50%,A2<60%),"E","F")))))

Now these formulas work perfectly. However they are complex and messy. 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.

=VLOOKUP(A2,$E$4:$F$9,2,TRUE)

Much simpler.

This is a range lookup, so it is essential that the first column of the lookup table (column E) is in ascending order.

First column must be in ascending order for range lookups

4. The Fantastic CHOOSE Function

This last alternative to the nested IF formula is a bit of a secret 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. And that makes it perfect for use with form controls.

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

The image below may look a little messy. 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.

The first 7 rows have data of the top goal scorers from 4 different football leagues in the 2016-17 season. A combo box is on the left and is linked to cell A9. A selection from the 4 leagues in the combo box will produce index number 1, 2, 3 or 4 in cell A9.

CHOOSE function as a nested IF formulas alternative

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

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.

=IF(A9=1,A3,IF(A9=2,D3,IF(A9=3,G3,IF(A9=4,J3))))

Or we could use this awesome and simple little CHOOSE function. It checks the index value in cell A9 and then returns the relevant information from its list.

=CHOOSE($A$9,A3,D3,G3,J3)

I hope you found these nested IF formula alternatives useful. Check out these other Excel formula tutorials.

  • Excel formula to find the least frequent value
  • Formula to match records from different worksheets
  • Tips for troubleshooting formulas

Related Posts:

  • Shrinking Drop-Down List using Dynamic Array Formulas
    Shrinking Drop-Down List using Dynamic Array Formulas
  • What You Need to Know About Dynamic Array Formulas in Excel
    What You Need to Know About Dynamic Array Formulas in Excel

Reader Interactions

Comments

  1. alex says

    24 December 2020 at 7:50 pm

    october sancho 10
    october sancho 20
    october sancho 30
    october sancho 40
    october neymar 50

    how could i calculate the top 3 scores for sancho in october? i have a data set where 1000 players in october and i need to sum the top three for each player for that month. i tried to do this =IF(AND(E17=C17:C21,F16=B17:B21),SUM(LARGE(D17:D21,{1,2,3}))””) but due to the last part of the if function since it says neymar at the bottom it leaves it blank.

    Reply
    • Alan Murray says

      24 December 2020 at 9:21 pm

      Hi Alex, you can use SUMPRODUCT. I have videos on that function. It is marvellous.
      The following formula does what you ask. It assumes the data you provide starts in A2. And that the month October is in cell F3 and the name Sancho is in E3
      =SUMPRODUCT(($B$2:$B$9=E3)*($A$2:$A$9=F3)*($C$2:$C$9=LARGE($C$2:$C$9,{1,2,3}))*($C$2:$C$9))
      Now there are alternative methods to this, especially because you want all months and names. But this works.
      In Excel 365, if you have that, the FILTER function can be used.

      Reply

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Primary Sidebar

Popular Posts

  • Excel Fixtures and League Table Generator
  • 5 Reasons Why your Excel Formula is Not Calculating
  • Excel IF Function Contains Text – A Partial Match in a Cell
  • Excel Formula to Display the Sheet Name in a Cell
  • 4 Alternatives to Nested IF Formulas
  • How to Hyperlink to a Hidden Worksheet in Excel
  • How to Create a Reverse FIND Formula
  • Check if a Value is an Even Number
  • IF Function in Power Query Including Nested IFS
  • Conditional Formatting Multiple Columns – 3 Examples

Recent Posts

  • Moving Average in Excel
  • Excel IMAGE Function – Insert Images from a Cell Value
  • SEQUENCE Function in Excel
  • Sort by Drop Down List in Excel
  • Advanced SUM Function Examples – The Power of SUM
Advanced Excel Success book

Footer

Contact

[email protected]

Useful Links

  • Home
  • Excel Online Courses
  • Tutorials
  • Sitemap
  • Privacy Policy
  • Jobs by Jooble

Course Topics

  • Charts
  • Courses
  • Dashboards
  • Downloads
  • Excel Tips
  • Formulas
  • Mail Merge
  • Office
  • PivotTables
  • Power BI
  • Power Pivot
  • Power Query
  • VBA

Join Our Mailing List

© Copyright 2020 Computergaga · All Rights Reserved ·