How to use VLOOKUP in Excel with multiple sheets – 4 methods

If you want to know how to use VLOOKUP formula in Excel with multiple sheets, you’ve landed on the right page.

The simplest way to use the VLOOKUP formula is in which all the data is on the same sheet. However, VLOOKUP can be used across multiple sheets as well. The simple VLOOKUP function can accommodate different sheets. But you can use other functions like IFERROR, MATCH, COUNTIF, and INDIRECT, which can make the formula complicated very quickly. 

In this guide, we will go through how to use VLOOKUP in Excel across multiple sheets and share four methods for the task.

How you can use VLOOKUP across multiple sheets in Excel 

Scenario on hand: We have a fictional sales dataset of L’Oreal’s beauty products stocked in a supermarket. Each month’s sales are recorded in different sheets:

Sheet 1: First Month

Sheet 2: Second Month

Sheet 3: Third Month

What we want to accomplish: We want to understand how to use VLOOKUP to find text across multiple sheets:

1. Using the simple VLOOKUP formula 
2. Using the VLOOKUP and IFERROR functions 
3. Using the VLOOKUP and IFERROR functions across multiple sheets
4. Using VLOOKUP, IFERROR, and other combined functions 

Method 1: Using the simple VLOOKUP formula 

With a slight addition of the sheet name, the VLOOKUP formula can be used in its simplest form. 

Here’s the syntax of the simple VLOOKUP formula used across multiple sheets:

=VLOOKUP({Lookup value},‘{Sheet name}’!{Table array}, {Column index number}, FALSE)

We used this formula to fetch the sales of “L’Oreal Infallible Pro-Matte Foundation” in the first month:

=VLOOKUP(B3,’Month 1′!$C$6:$E$12, 3, FALSE)

Note: We use the $ sign in the table array to lock in the table (convert it into an absolute cell reference). Now, this table array will not change when we use the Fill Handle tool to expand the formula across the table. 

Here’s how we expand this formula:

Here are the resulting values:

We change the sheet name in the formula to fetch data for the other two columns:

For Month 2 Sales, the formula is: =VLOOKUP(B3,’Month 2′!$C$6:$E$12, 3, FALSE)

For Month 3 Sales, the formula is: =VLOOKUP(B3,’Month 3′!$C$6:$E$12, 3, FALSE)

Here’s the final table:

Note that we are getting #N/A values because the product names and values were absent in that sheet.

Method 2: Using the VLOOKUP and IFERROR function 

The IFERROR function can be used with VLOOKUP to return a text in case of an #N/A error. 

Here’s the formula we use to return the text “Not in stock” in case the VLOOKUP formula does not find the product’s name in the table array:

=IFERROR(VLOOKUP{Lookup value},‘{Sheet name}’!{Table array}, {Column index number}, FALSE), “{Text in place of #N/A”))

=IFERROR(VLOOKUP(B3,’Month 1′!$C$6:$E$12,3,FALSE),”Not in stock”)

Here’s the table now:

Method 3: Using the VLOOKUP and IFERROR functions across multiple sheets

While IFERROR can help remove the #N/A error, it also comes in handy to search for text across multiple sheets. 

Let’s suppose that the Sales sheets in the first month were not consolidated. In this case, we have to search across two sheets to return the sales figure in Month 1. 

Here’s the formula for that:

=IFERROR(VLOOKUP{Lookup value},‘{Sheet name}’!{Table array}, {Column index number}, FALSE), IFERROR(VLOOKUP{Lookup value},‘{Sheet name2}’!{Table array}, {Column index number}, FALSE),”{Text in place of #N/A”))

We applied this formula:

=IFERROR(VLOOKUP(B3,’Month 1′!$C$6:$E$12,3,FALSE), IFERROR(VLOOKUP(B3,’Month 1 (2)’!$C$6:$E$13,3,FALSE),”Not in stock”))

Note that we have to add another IFERROR argument in the formula for every new sheet we add. 

Here are the results using this formula:

The formula did not find this product in the first sheet, but it searched for it in the second sheet and returned the value. 

Method 4: Using VLOOKUP, IFERROR, and other combined functions 

We wish to find the sales of each L’Oreal product when first stocked at the supermarket. For that, we need to look across the sheets for the three months and return the sales value of the first occurrence of each product. 

We use the following formula:

=IFERROR(VLOOKUP({Cell address of lookup value} ,INDIRECT(“‘”&INDEX({Sheet names table array} ,1,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&{Sheet names table array}&”‘!{column array where lookup value exists}”),{Cell address of lookup value})>0,0))&”‘!{Table array of data across the sheets}”),{Column index number},FALSE),”{Text to return if not found}”)

Here’s the formula we used to fetch for the sales values:

=IFERROR(VLOOKUP(B3,INDIRECT(“‘”&INDEX($E$3:$G$3,1,MATCH(TRUE,COUNTIF(INDIRECT(“‘”&$E$3:$G$3&”‘!C6:C12″),B3)>0,0))&”‘!$C$6:$E$12″),3,FALSE),”Not found”)

Here’s the result:

Since we have used $ in the formula to lock the table arrays, we can drag the formula across the table using the Fill Handle tool:

What this formula does is index the sheet names and product names. 

Wrapping up

This was everything you needed to know about how to use VLOOKUP formula in Excel with multiple sheets. VLOOKUP can be used to search for text across different sheets. You can either enter the sheet name manually or use functions like INDEX, MATCH, and INDIRECT to fetch the sheet names automatically. Manually entering the sheet names yields a simple VLOOKUP formula. But you need to use the nested function with IFERROR, INDEX, MATCH, and INDIRECT if you have data spread across several sheets. 

Learn more about how the VLOOKUP function works in Excel through these helpful guides:

• Finding the second match with VLOOKUP in Excel
• Why VLOOKUP returns #N/A when match exists in Excel?
• Guide to VLOOKUP Fuzzy Match in Excel