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

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`

.

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)*

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
`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

and Excel’s formula evaluator will take you through the entire formula step-by-step. This is shown below.**Formulas**>**Formula Auditing**>**Evaluate Formula**

Multiple Choice Question

Select the correct option:

**a) You must have a common column in order to do a**`DOUBLE VLOOKUP`

formula.**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.