How to Use Nested VLOOKUP in Excel? A Step-by-Step Guide

(Note: This guide on how to use nested VLOOKUP in Excel is suitable for all Excel versions including Office 365)

Most of us have become familiar with VLOOKUP in Excel over the years. So many guides and tutorials have been made about this wonderful function. We know that it is used to look up values from databases. However, did you know that you can actually nest VLOOKUP formulas together? 

Yes, in fact, you can keep nesting VLOOKUP functions together as much as you need. Now, you might be wondering what is the purpose of all this? 

Well, the answer is very simple. Sometimes, you may be required to lookup values which are interlinked via several tables. Now, instead of using separate VLOOKUP formulas to extract each one of the intervening values, you can nest all of them together and arrive at the final value in one go. 

Sounds confusing? 

Don’t worry! It will all be clear as day after I explain this with an example. 

How to Use Nested VLOOKUP in Excel?

Let us say, for example, I have a spreadsheet where I have two separate tables. The first table lists all the book names along with their corresponding IDs. The second table lists the price of each book ID. 

Now, if I ask you to find the price of a certain book based on its name, you can use two separate VLOOKUP formulas as shown below: 

Here, the first VLOOKUP formula =VLOOKUP(G9,I3:J5,2) looks for the book’s name and returns the corresponding ID from Table1. 

And the second VLOOKUP formula =VLOOKUP(H9,L3:M5,2) looks for the book’s ID and returns the corresponding price from Table2. 

Pretty simple, isn’t it?

But, it is definitely a roundabout way of doing this. 

Why can’t we just combine these two VLOOKUP formulas together and directly get the price data in one go? 

All you have to do is nest the first VLOOKUP formula inside the input of the second VLOOKUP formula. 

It will look something like this: 

Here, the inner VLOOKUP looks for the book’s name and returns the ID. The outer VLOOKUP looks for the book’s ID and returns its price from Table 2. 

That’s all folks! This is nested VLOOKUP in a nutshell. Using nested VLOOKUPs will save you a lot of time and space, especially when multiple sheets and complex tables are involved. 

What is even better about this is that you can keep nesting VLOOKUPs together as much as you want. By doing this, you can skip several steps and avoid a lot of confusion. 

Nested VLOOKUP Syntax

To summarize what we just discussed:

You can nest multiple VLOOKUPs together to skip unnecessary calculations. 

The general syntax is:

VLOOKUP_final(VLOOKUP_initial, Final_Table, Index)

Where VLOOKUP_final returns the final value and VLOOKUP_initial returns any intermediary values. 

How to Use Conditional VLOOKUP? 

Another interesting use case of nested VLOOKUPs is to look up values across multiple tables. 

Let us say, you have three different tables (let us call them A, B and C) which list various book IDs and their corresponding prices. 

Now, you want to look up the price of a certain book ID, but you are not sure where it is listed. 

You can easily do this by combining nested VLOOKUPs and the IFERROR function together by using the following formula syntax:

IFERROR(VLOOKUP_TABLEA(IFERROR(VLOOKUP_TABLEB,VLOOKUP_TABLEC)))

Here, the IFERROR traps any #N/A error and redirects Excel to look for the value in all the tables A, B, and C. 

In this example, the formula will look like this: 

=IFERROR(VLOOKUP(F12,C5:D7,2),IFERROR(VLOOKUP(F12,F5:G7,2),VLOOKUP(F12,I5:J7,2)))

In short, this formula looks for the value in Table A first. If it fails, then it looks for the value in Table B next. If it still fails, it looks for the value in Table C last. 

The key modification here is the IFERROR function. It catches all errors and runs alternate VLOOKUPs if that is the case. 

Closing Thoughts

In this guide, we saw how to use nested VLOOKUP in Excel. We also discussed the merits and use cases of nested VLOOKUPs. This may seem confusing at first, but it is pretty straightforward. I recommend you try using these functions in a rough spreadsheet and see for yourself. 

Please let me know if you have any questions. We are always happy to help. 

If you need more high-quality Excel guides, please check out our free Excel resources center.

Simon Sez IT has been teaching Excel for over ten years. For a low, monthly fee you can get access to 130+ IT training courses. Click here for advanced Excel courses with in-depth training modules.

Simon Calder

Chris “Simon” Calder was working as a Project Manager in IT for one of Los Angeles’ most prestigious cultural institutions, LACMA. He taught himself to use Microsoft Project from a giant textbook and hated every moment of it. Online learning was in its infancy then, but he spotted an opportunity and made an online MS Project course - the rest, as they say, is history!