The Excel Double VLOOKUP Trick (Nesting VLOOKUPs)

The Excel Double VLOOKUP Trick (Nesting VLOOKUPs)

<<< Go back to Part I: Basic VLOOKUP

Level: Advanced

Download the Excel file to follow along

We have already seen how to use the basic VLOOKUP function at length, in a previous tutorial.

We are now going to look at a more advanced situation, where we will need to use more than one VLOOKUP function within a single formula. So if you are unfamiliar with the VLOOKUP function or its syntax, you will need to first review our previous VLOOKUP tutorial, in order to grasp how to accomplish the more advanced functionality in this tutorial.

So let’s get started and look at a typical scenario that requires the use of two VLOOKUPs within one formula. This situation is referred to as double VLOOKUPs or nesting VLOOKUPs within the same formula.

Double VLOOKUP Example

A hypothetical baseball scout visited a few different states, created a list of players and recorded his evaluation of their skill level in an Excel workbook. He recorded their Player ID (the unique identifier that identifies each player), their names, the state they are from, their ages and their overall rating. The baseball scout calculated the overall rating by looking at a combination of factors namely the batting average, hitting power and speed of the player at hand and based on the above factors, gave the player a rating of good, fair or poor.

A different baseball scout visited the same states at a later stage, and this time was tasked with recording the physical condition, of the same players that the other scout previously evaluated. This scout recorded the names of the players and their physical condition, in terms of whether they were fit, unfit or injured and recorded this in another table on the same worksheet, in the same workbook.

The source data is shown below.

 

excel nested vlookup

 

1) We’d like to enter the Player ID in cell A3 and then have the corresponding physical condition shown in cell B3. However, as we can see this data is stored in two different tables/ranges. The common column in both tables/ranges is Player Name though. So, we will have to use this column in our formula, in order to form a bridge or link between the two tables/ranges and deliver the needed matching data from the second table, in cell B3.

 

double vlookup excel

 

2) In order to retrieve the physical condition of the player in cell B3, based on the Player ID given in cell A3, we enter the following formula in cell B3:

=VLOOKUP(VLOOKUP(A3,A6:E21,2,FALSE),G6:H21,2,FALSE)

 

excel double vlookup nested vlookup

 

3) Upon pressing CTRL-ENTER, the formula with the nested VLOOKUPs, returns the value of Injured in cell B3 as shown below.

 

microsoft excel nested vlookup

 

So let’s review this process in a little bit more detail:

  • We start by entering the equals sign, then VLOOKUP and then an open parenthesis.
  • Instead of a direct lookup_value as in the case of a simple VLOOKUP example, we use another VLOOKUP to relate our value in A3 (Player ID) to it’s matching name (Player Name) retrieved from our first table/range (A6:E21). This has to be processed first.
  • So in the case of B001, the matching name sourced from the first table/range (A6:E21) is James Roth.
  • In the first table/range(A6:E21), Player Name is the second column so col_index_num is 2.
  • The (VLOOKUP(A3,A6:E21,2,FALSE) portion of the formula will give us James Roth in this case – the name that matches the Player ID in cell A3.
  • So once the matching Player Name is retrieved using the nested VLOOKUP, our first VLOOKUP now has a lookup_value, Player Name.
  • The next step within the formula evaluation involves using this lookup_value (Player Name) to find the corresponding physical condition for this player in the second table (G6:H21).
  • In the second table/range, col_index_num is also 2 since Physical Condition is column 2 in the second table/range.
  • So, our formula then uses the name delivered to retrieve the physical condition from the second table, and we can see how we used the common column to form a bridge between the two separate tables/ranges.
  • So for this particular example, James Roth is injured so ultimately a value of injured is delivered in B3.
  • The key to understanding DOUBLE VLOOKUPs is evaluating the formula in a step-by-step manner.
  • You can do this by selecting cell B3 and then going to Formulas Formula Auditing Evaluate Formula and Excel’s formula evaluator will take you through the entire formula step-by-step. This is shown below.

 

nested double vlookup

 

 

test your understanding
Multiple Choice Question

Select the correct option:

  1. a) You must have a common column in order to do a DOUBLE VLOOKUP formula.
  2. b) You don’t have to have a common column in order to do a DOUBLE VLOOKUP formula.

 

Conclusion

Nesting VLOOKUPs or the DOUBLE VLOOKUP trick is admittedly more complicated to understand than a simple VLOOKUP formula.

However, once you have mastered this and understand how to nest the VLOOKUP function, you will be able to look up matching data in two separate tables/ranges based on a common column. This is a scenario that often occurs so its worthwhile taking the time to master this technique.