Nested IF vs. VLOOKUP – Which one to use?

When you are uncertain….. Draw a flowchart to decide!

A typical example of using Nested IF (unnecessarily) is to assign a grade to a test score, e.g.

  • below 50 –> Fail
  • 50-59 –> E
  • 60-69 –> D
  • 70-79 –> C
  • 80-89 –> B
  • 90 or above –> A

This can be represented by a flowchart:

Excel Tips - Nested IF 1

With the steps discussed in the previous post, the following nested IF formula can be constructed easily.


Here’s a side-by-side image of the flowchart and the formula for better illustration:

Excel Tips - Nested IF 3

Tip: It is a good idea to separate nested if statement line by line for better readability.  To insert line break, press Alt+Enter whiling editing.

Try to practice nested IF on your own.  It is not that difficult once you used to follow the flow of the chart.

Nevertheless, the same can be achieved with a much better formula:

  • where Score is lookup value;
  • tbl_Grade is the lookup table for the grade, construct in the way as shown below:

Excel Tips - Nested IF 4

  • 2 is the column_index in this example as Grade is located on the 2nd column in the table while the lookup value (score) is on the 1st column
  • The final argument of the VLOOKUP is omitted, meaning an approximate match


  • If you data type is categorical, e.g. red, green, blue etc, you may use VLOOKUP with exact match (False as the final argument) and you don’t have to care about the sorting order in the lookup table
  • If you data type is real number, and you want to categorize the number, you may use VLOOKUP with approximate match (TRUE or omit the the final argument).  Nevertheless, the data in the lookup data must be sorted in ascending order.

For more information of VLOOKUP with approximate match, please read HERE.

Now let’s take a look at our example used in previous post.

Excel tips - IF(3)

Can we use VLOOKUP instead of nested IF?

Yes we can, with the help of a a two-way table and MATCH function.

A two-way table below lays out the scenarios clearly.Excel Tips - Nested IF 5

With this table, we may construct the VLOOKUP with MATCH to solve the question:


Excel Tips - Nested IF 6

Is this formula above better than the nested IF below?

=IF(B2>=B1,IF(B3=0,"Good job","Serve better"),IF(B3=0,"Sell more","In trouble"))

Well, maybe… hmmmm…. not really…

Let’s wrap it up:

When we have one branch only in the flowchart, like the example we have in this post,

Excel Tips - Nested IF 1

Nested IF


When we have two branches in the flowchart, like the example in the previous post:

Excel tips - IF(3)

Nested IF
=IF(B2>=B1,IF(B3=0,"Good job","Serve better"),IF(B3=0,"Sell more","In trouble"))

VLOOKUP wins in terms of length… However I can understand that you may prefer nested IF if you are a novel Excel user


If your flowchart contains

  • one branch only, use VLOOKUP (approximate or exact match according to your data type)
  •  two branches, consider 2D VLOOKUP (i.e. using MATCH for column_index) or Nested IF depending on your level of using functions
  • three branches or more, consider nested IF (when there is no better alternative)
  • whichever the case,

A well-drawn flowchart or a well-organized table helps you write a successful formula effectively

This entry was posted in Excel Tips and tagged , , . Bookmark the permalink.

4 Responses to Nested IF vs. VLOOKUP – Which one to use?

  1. david says:

    why are the formula are able to recognize that a grade falls in a range

    namely 50 to 59
    60 to 69

    and so on

    without having to enter that range into the formula


  2. Deepak says:

    A similar approach for these types of nested if..



    • MF says:

      Hi Deepak,
      Thanks for your suggestion. LOOKUP a would do the same job as VLOOKUP does here.
      Since we have already built the table of grade (tbl_Grade), we may use that instead of hard-coding it into the formula.
      More flexible for modification, just in case. 🙂


Comments, suggestions, corrections are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s