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.
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
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
3) Upon pressing
CTRL-ENTER, the formula with the nested
VLOOKUPs, returns the value of
Injured in cell
B3 as shown below.
So let’s review this process in a little bit more detail:
- We start by entering the equals sign, then
VLOOKUPand then an open parenthesis.
- Instead of a direct
lookup_valueas in the case of a simple
VLOOKUPexample, we use another
VLOOKUPto relate our value in
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
- 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 IDin cell
- So once the matching
Player Nameis retrieved using the nested
VLOOKUP, our first
VLOOKUPnow has a
- The next step within the formula evaluation involves using this
Player Name) to find the corresponding physical condition for this player in the second table (
- In the second table/range,
col_index_numis also 2 since
Physical Conditionis 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
- The key to understanding
DOUBLE VLOOKUPsis evaluating the formula in a step-by-step manner.
- You can do this by selecting cell
B3and then going to
Formulas > Formula Auditing > Evaluate Formulaand Excel’s formula evaluator will take you through the entire formula step-by-step. This is shown below.
Multiple Choice Question
Select the correct option:
- a) You must have a common column in order to do a
- b) You don’t have to have a common column in order to do a
VLOOKUPs or the
DOUBLE VLOOKUP trick is admittedly more complicated to understand than a simple
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.