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

Here are four different methods!

You can trust PC GuideOur team of experts use a combination of independent consumer research, in-depth testing where appropriate – which will be flagged as such, and market analysis when recommending products, software and services. Find out how we test here.

Last Updated on

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: