Excel VLOOKUP Multiple Sheets in 10 Easy Steps (+ Example Workbook)

In this Excel VLOOKUP Multiple Sheets Tutorial, you learn how to carry out a VLOOKUP across multiple sheets.

The VLOOKUP multiple sheets formula template/structure you learn in this Tutorial isn't the only way to carry out a VLookup across multiple sheets. You may (also) be interested in the following alternative formula template/structures:

Excel VLOOKUP Multiple Sheets in Different Workbook.
Excel VLOOKUP Sheet in Multiple Different Workbooks.

I link to these Tutorials in the Related Excel Training Materials and Resources Section below.

This Excel VLOOKUP Multiple Sheets Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process below. Get this example workbook (for free) by clicking the button below.

Table of Contents

This Excel VLOOKUP Multiple Sheets Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

Excel VLOOKUP Tutorial (under development): Click here to open.
Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
Excel VLOOKUP Compare 2 Columns and Find Matches in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Row Values (in Same Column) in 4 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Columns in 5 Easy Steps: Click here to open.
Excel VLOOKUP Sum Multiple Columns (Values) in 6 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Column Values (with XLOOKUP) in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Rows and Columns in 3 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the INDEX Function in 7 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Sheets in Different Workbook in 10 Easy Steps: Click here to open.
Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps: Click here to open.

My Excel XLOOKUP Tutorial (click here to open) may help you:

Better understand and implement the contents below.
Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

Excel Macro Tutorial for Beginners: Click here to open.
Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
350 + Excel Keyboard Shortcuts And Hotkeys: Click here to open.

If you want to learn more about Excel essentials, Excel formulas, and similar Excel topics, you may be interested in taking one (or more) Excel Courses: Click here to learn more about these Excel Courses (affiliate link). The following are some of the topics covered in these Excel Courses:

Excel essentials and must-know skills.
Advanced Excel Formulas.
Excel Tables.
Pivot Tables.
Dashboards.
Power Pivot.
Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.

If you need consulting services, you may want to consider working with ExcelRescue. ExcelRescue is my usual suggestion for people who (like you) may need help with Excel tasks/projects: Click here to visit ExcelRescue (affiliate link).

The VLOOKUP Multiple Sheets Formula Template/Structure

The following is the VLOOKUP multiple sheets formula template/structure I explain (step-by-step) in the Sections below.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
=VLOOKUP(LookupValue,INDIRECT("'"&INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"'!AddressOfTableYouLookIn"),NumberOfColumnWithValueToReturn,FALSE)

This is an array formula. If you're working with Excel 2019 or earlier, enter this formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
{=VLOOKUP(LookupValue,INDIRECT("'"&INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"'!AddressOfTableYouLookIn"),NumberOfColumnWithValueToReturn,FALSE)}

The Example Before VLOOKUP Multiple Sheets

The example workbook has (before I add the VLOOKUP multiple sheets example formulas) 4 worksheets with the characteristics I describe below.

(1) Data Worksheets (VLookup Sheet 1, VLookup Sheet 2, VLookup Sheet 3).

Each worksheet has a single table (cells A8 to G28). The tables' layout is the same (across worksheets). The VLOOKUP multiple sheets formula template/structure you learn in this Tutorial assumes the tables' layout remains the same (across worksheets).

These are the tables:

With the data.
Where you search with the VLOOKUP multiple sheets example formulas.

Main characteristics:

7 columns (Salesperson, Date, Customer, Product, City, Units, Unit Price).
1 header row (row 8).
20 entries (rows 9 to 28).

(1) VLookup Sheet 1.

Worksheet data 1 for VLOOKUP multiple sheets

(2) VLookup Sheet 2.

Worksheet data 2 for VLOOKUP multiple sheets

(2) VLookup Sheet 3.

Worksheet data 3 for VLOOKUP multiple sheets

(2) Before Worksheet.

This worksheet has a single table (cells A6 to D13). This is the table where you:

Set up the VLOOKUP multiple sheets example formulas.
Display the results.

This table has the following 4 columns:

Column: Cells A7 to A13 store numbers. These numbers match the column numbers (see cells A6 to G6) of the tables in the Data Worksheets (VLookup Sheet 1, VLookup Sheet 2, VLookup Sheet 3; the tables the VLOOKUP multiple sheets example formulas work with).
Header: The values in cells B7 to B13 match the headers of the tables in the Data Worksheets (VLookup Sheet 1, VLookup Sheet 2, VLookup Sheet 3; cells A8 to G8).
Value:
- Cell C7 contains the lookup value (the value you search for).
- Cells C8 to C13:
  - Are currently empty.
  - Will store the VLOOKUP multiple sheets example formulas.
Formula: Cells D8 to D13:
- Currently display the #N/A error.
- Will display the VLOOKUP multiple sheets example formulas I enter in cells C8 to C13.

Step 1: Create List of Multiple Sheets

Create a list with the names of the multiple sheets where you carry out the VLOOKUP across multiple sheets.

Consider, for example:

Entering the multiple sheet list in a worksheet column; and
Naming the applicable cell range by, for example:
1. Selecting the cell range containing the multiple sheet list.
  - Select (only) the multiple sheet list names.
  - Don't select any header(s).
2. Clicking the Name Manager button in the Formulas tab of the Excel Ribbon. Excel displays the Name Manager dialog box.
3. Clicking the New button inside the Name Manager dialog box. Excel displays the New Name dialog box.
4. Doing the following in the New Name dialog box:
  1. Entering the named cell range's name in the Name text box.
  2. Ensuring the address displayed in the Refers to text box is correct (refers to the multiple sheet list).
  3. Click the OK button.

Step 1 Example

I work with 3 worksheets:

VLookup Sheet 1.
VLookup Sheet 2.
VLookup Sheet 3.

I create a list with the names of these multiple sheets in column F (cells F6 to F9) of the worksheet where I set up the VLOOKUP multiple sheets example formulas.

Cell F6 is a header (VLookup Multiple Sheet List).
Cells F7 to F9 contain the multiple sheets' names.

Step 2: Create an Array with Text References to the Column (in the Multiple Sheets) You Look In

Create an array with text references to the column (in each of the multiple sheets) you look in. Each individual text reference (inside the array) must include 3 items:

The worksheet name, wrapped in single quotes (‘WorksheetName').
An exclamation sign (!).
The address of the column you look in.

To achieve this, consider working with the ampersand (&) text concatenation operator to concatenate 3 items:

A string with a single quote (“‘”).
A reference to the multiple sheet list you created in step #1.
A string with 3 items:
1. A single quote (‘).
2. An exclamation sign (!).
3. The address (for example, entered as text) of the column you look in.
  - As a general rule: This is a single column (1 column, multiple rows) cell range.
  - This is the column where you search for the lookup value (you specify in step #4).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
"'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"

Step 2 Example

I look in column A (cells A9 to A28) of the multiple sheets. I use the ampersand (&) text concatenation operator to concatenate the following 3 items:

A string with a single quote: “‘”.
An absolute reference to the multiple sheet list I created in step #1: $F$7:$F$9.
A string with 3 items (“‘!$A$7:$A$26”):
1. A single quote: ‘.
2. An exclamation sign: !.
3. The address (with absolute references, and entered as text) of the column I look in: $A$9:$A$28.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
"'"&$F$7:$F$9&"'!$A$9:$A$28"

The image below displays how this expression returns an array with fully qualified text references (worksheet name wrapped in single quotes, exclamation sign, column address) to the column (in each of the multiple sheets) I look in.

Column F (VLookup Multiple Sheet List) contains the multiple sheet list I created in step #1.
Column G (Step 2 Column Text Reference) displays the text reference returned by the expression I describe above.
Cell H7 displays the formula (=”‘”&$F$7:$F$9&”‘!$A$9:$A$28″) stored in column G.

References to column you look in for VLOOKUP multiple sheets

Step 3: Obtain an Array with the Values Stored in Each Column (in the Multiple Sheets) You Look In

Use the INDIRECT function to obtain the values stored in each column (in each of the multiple sheets) you look in.

The INDIRECT function returns a reference specified by a text string. The references returned by the INDIRECT function are evaluated to display/return the applicable (referred cells') contents.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDIRECT(ref_text)

To obtain the values stored in each column (in each of the multiple sheets) you look in when creating a VLOOKUP multiple sheets formula, set the ref_text argument of the INDIRECT function to the text references (you created in step #2) to the column (in each of the multiple sheets) you look in.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn")

Step 3 Example

Considering the text references I created in step #2 to cells A9 to A28 in each of the multiple worksheets (VLookup Sheet 1, VLookup Sheet 2, VLookup Sheet 3) I look in:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28")

The image below illustrates how this expression returns the values stored in each column (in each of the multiple sheets) I look in.

Columns I to K (“VLookup Sheet 1” INDIRECT Output, “VLookup Sheet 2” INDIRECT Output, “VLookup Sheet 3” INDIRECT Output) display the values stored in each column in each of the multiple sheets I look in.
- Column I (cells I9 to I28) displays the values stored in column A (cells A9 to A28) of the “VLookup Sheet 1” worksheet.
- Column J (cells J9 to J28) displays the values stored in column A (cells A9 to A28) of the “VLookup Sheet 2” worksheet.
- Column K (cells K9 to K28) displays the values stored in column A (cells A9 to A28) of the “VLookup Sheet 3” worksheet.
Row 6 (cells I6 to K6) displays the formulas stored in rows 9 to 28 of the applicable column.
- These formulas follow the basic structure of the expression I describe above (=INDIRECT(“‘”&$F$7:$F$9&”‘!$A$9:$A$28”)).
- But (for illustrative purposes in this Tutorial) refer to a single cell in the multiple sheet list ($F$7, $F$8, $F$9) I created in step #1 (instead of referring to the entire list; $F$7:$F$9). In other words:
  - The formulas in column I refer to (only) cell F7 ($F$7).
  - The formulas in column J refer to (only) cell F8 ($F$8).
  - The formulas in column K refer to (only) cell F9 ($F$9).

Values stored in multiple data sheets for VLOOKUP

Step 4: Count the Number of Times the Lookup Value Appears in the Column You Look In Inside Each of the Multiple Sheets

Use the COUNTIF function to count the number of times:

The lookup value (the value you search for);
Appears in the column (you refer to in step #2);
In each of the multiple sheets you work with (and included in the multiple sheet list you created in step #1).

The COUNTIF function counts the number of cells inside a cell range that meet a specific condition.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
COUNTIF(range,criteria)

To count the number of times the lookup value appears in the column you look in (inside each of the multiple sheets) when creating a VLOOKUP multiple sheets formula, specify the arguments of the COUNTIF function as follows:

Range: The reference returned by the INDIRECT function (you created in step #3).
Criteria: The value you search for (the lookup value) in the column (in each of the multiple sheets) you specified in step #2.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
COUNTIF(INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"),LookupValue)

The COUNTIF function (criteria) is case insensitive.

Step 4 Example

The lookup value I work with is stored in cell C7 (using absolute references: $C$7).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)

The image below displays how this expression returns an array with the number of times the lookup value appears in column A inside the applicable sheet.

Column F (VLookup Multiple Sheet List) contains the multiple sheet list I created in step #1.
Column G (Lookup Value Count) displays the number of times the lookup value stored in cell C7 (Salesperson 7) appears in column A inside the applicable sheet (listed in column F).
Cell H7 displays the formula (=COUNTIF(INDIRECT(“‘”&$F$7:$F$9&”‘!$A$9:$A$28”),$C$7)) stored in column G.

Count lookup value appearances for VLOOKUP multiple sheets

The lookup value (Salesperson 7):

Doesn't appear (Lookup Value Count is 0) in:
- The “VLookup Sheet 1” worksheet.
- The “VLookup Sheet 2” worksheet.
Appears 1 time (Lookup Value Count is 1) in the “VLookup Sheet 3” worksheet.

Step 5: Test Whether the Lookup Value Appears in the Column You Look In (Inside Each of the Multiple Sheets)

The COUNTIF function (you created in step #4) returns the number of times the lookup value appears in the column you look in (inside each of the multiple sheets).

Use the greater than (>) comparison operator to:

Test whether the value returned by the COUNTIF function (you created in step #4) is greater than 0 ((COUNTIF(range,criteria))>0); and
Convert the values returned by the COUNTIF function (you created in step #4) into Boolean values (TRUE or FALSE).

The resulting expression returns an array with TRUEs and FALSEs, depending on whether the lookup value appears (or not) in the column you look in inside each of the multiple sheets.

TRUE: The lookup value appears (at least once) in the column you look in (inside the applicable sheet).
FALSE: The lookup value doesn't appear in the column you look in (inside the applicable sheet).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
COUNTIF(INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"),LookupValue)>0

Step 5 Example

Considering the COUNTIF function I created in step #4:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0

The image below displays how this expression returns an array with TRUEs and FALSEs, depending on whether the lookup value appears (or not) in column A (inside each of the multiple sheets).

Column F (VLookup Multiple Sheet List) contains the multiple sheet list I created in step #1.
Column G (Lookup Value Appears in Column) displays a Boolean value (TRUE or FALSE).
- TRUE: The lookup value stored in cell C7 (Salesperson 7) appears in column A inside the applicable sheet (listed in column F).
- FALSE: The lookup value stored in cell C7 (Salesperson 7) doesn't appear in column A inside the applicable sheet (listed in column F).
Cell H7 displays the formula (=COUNTIF(INDIRECT(“‘”&$F$7:$F$9&”‘!$A$9:$A$28”),$C$7)>0) stored in column G.

Test lookup value appearance in worksheet for VLOOKUP multiple sheets

Step 6: Identify the Position (Inside the Multiple Sheet List) of the First Worksheet Where the Lookup Value Appears (in the Applicable Column)

Use the MATCH function to identify the relative position:

Of the first worksheet where the lookup value appears (in the applicable column);
Inside the multiple sheet list (you created in step #1).

In the VLOOKUP multiple sheets formula, the MATCH function does the following:

Search for an item in an array; and
Return the relative position of the item in the array.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
MATCH(lookup_value,lookup_array,match_type)

To find the relative position of the first worksheet (inside the multiple sheet list) where the lookup value appears (in the applicable column), specify MATCH's arguments as follows:

lookup_value: 1.
lookup_array: An array of 0s and 1s created by doing the following to the conditional test you created in step #5 (COUNTIF(range,criteria)>0):
- Wrapping the conditional test in parentheses ((COUNTIF(range,criteria)>0)); and
- Prepending 2 minus signs (–; double unary) prior to the opening parenthesis (–(COUNTIF(range,criteria)>0)).
  - The conditional test you created in step #5 returns an array of Boolean values (TRUE or FALSE).
  - Prepending 2 minus signs (–; double unary) coerces those Boolean values (TRUE and FALSE) to the numbers 1 and 0.
    - TRUE = 1.
    - FALSE = 0.
match_type: 0.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
MATCH(1,--(ConditionalTestFromStep5),0)

Considering the conditional test you created in step #5:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
MATCH(1,--(COUNTIF(INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"),LookupValue)>0),0)

Consider the following when setting up the MATCH function inside the VLOOKUP multiple sheets formula:

As a general rule: If the lookup value is found in the applicable column inside more than 1 sheet:
- The array returned by the conditional test you created in step #5 will contain more than 1 TRUE.
- The (multiple) TRUEs returned by the conditional test (you created in step #5) are converted (by the 2 minus signs you add in this step #6) to 1s.
- The MATCH function works with the first entry matching the lookup_value (1). This is the first sheet (inside the multiple sheet list) where the lookup value appears (in the applicable column).
If the lookup value isn't found in the applicable column inside any of the sheets:
- The conditional test you created in step #5 returns an array of (only) FALSEs.
- The FALSEs returned by the conditional test (you created in step #5) are converted (by the 2 minus signs you add in this step #6) to 0s.
- The MATCH function:
  - Cannot find an exact match for the lookup_value (1); and
  - Returns the #N/A error.

Step 6 Example

Considering the conditional test I created in step #5:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0)

Position of first worksheet where lookup value appears for VLOOKUP multiple sheets

The lookup value (Salesperson 7) appears in the third worksheet inside the multiple worksheet list. This is the “VLookup Sheet 3” worksheet.

Step 7: Obtain the Name of the Sheet Where the Lookup Value Appears (in the Applicable Column)

Use the INDEX function to extract (from the multiple sheet list you created in step #1) the name of the sheet where the lookup value appears (in the applicable column).

In the VLOOKUP multiple sheets formula, the INDEX function returns the value (sheet name) in a specific row inside a single-column (1 column, multiple rows) array (the multiple sheet list).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDEX(reference,row_num)

To obtain the name of the sheet where the lookup value appears (in the applicable column), specify INDEX's arguments as follows:

Reference: A reference to the multiple sheet list you created in step #1.
Row_num: The number returned by the MATCH function you created in step #6.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDEX(MultipleSheetList,MatchFunctionFromStep6)

Considering the MATCH function (you created in step #6):

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"),LookupValue)>0),0))

Step 7 Example

(1) Considering the multiple sheet list I created in step #1:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDEX($F$7:$F$9,MatchFunctionFromStep6)

(2) Considering the MATCH function I created in step #6:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))

Name of worksheet where lookup value first appears for VLOOKUP multiple sheets

The name of the worksheet where the lookup value (Salesperson 7) appears is “VLookup Sheet 3”.

Step 8: Obtain an Array with the Values Stored in the Table Where You Look In Inside the Sheet Where the Lookup Value Appears

Use the INDIRECT function to obtain the values stored in the table (the cell range with the applicable data) where you look in (inside the sheet where the lookup value appears).

The INDIRECT function returns a reference specified by a text string. The references returned by the INDIRECT function are evaluated to display/return the applicable (referred cells') contents.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDIRECT(ref_text)

Do the following to obtain the values stored in the table (cell range) where you look in (inside the sheet where the lookup value appears) when creating a VLOOKUP multiple sheets formula:

(1) Consider working with the ampersand (&) text concatenation operator to concatenate 3 items:

A string with a single quote (“‘”).
The INDEX function you created in step #7.
A string with the following 3 items:
1. A single quote (‘).
2. An exclamation sign (!).
3. The address (for example, entered as text) of the table (cell range) where you look in.
  - The first/leftmost column of the table (you refer to in this step) must contain the lookup value (you specified in step #4).
  - As a general rule: If the first/leftmost column in the table contains duplicate values (and you look up one of those duplicate values), the VLOOKUP function (you create in step #9) works with the first entry matching the lookup value.
  - The table (you refer to in this step) must (also) contain the column with the value to return.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
"'"&INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"'!AddressOfTableYouLookIn"

(2) Set the ref_text argument of the INDIRECT function to the new (concatenated) string (returned by the ampersand (&) text concatenation operator).

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDIRECT("'"&INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"'!AddressOfTableYouLookIn")

Step 8 Example

The table (cell range) I look in contains cells A9 to G28. I use the ampersand (&) text concatenation operator to concatenate the following 3 items:

A string with a single quote: “‘”.
The INDEX function I created in step #7: INDEX($F$7:$F$9,MATCH(1,–(COUNTIF(INDIRECT(“‘”&$F$7:$F$9&”‘!$A$9:$A$28”),$C$7)>0),0)).
A string with 3 items (“‘!$A$9:$G$28”).
1. A single quote: ‘.
2. An exclamation sign: !.
3. The address (with absolute references, entered as text) of the table (cell range) I look in: $A$9:$G$28″.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28")

The image below displays how this expression returns an array with the values stored in the table (the cell range with the applicable data) where I look in (inside the sheet where the lookup value appears).

Columns H to N (cells H8 to N27) display the data returned by the INDIRECT function I describe above.
- For purposes of this Tutorial, I do some additional number formatting.
Cell I6 displays the formula (=INDIRECT(“‘”&INDEX($F$7:$F$9,MATCH(1,–(COUNTIF(INDIRECT(“‘”&$F$7:$F$9&”‘!$A$9:$A$28″),$C$7)>0),0))&”‘!$A$9:$G$28”)) stored in cells H8 to N27.

Step 9: Carry out the VLookup

Work with the VLOOKUP function to carry out the VLookup multiple sheets.

In the VLOOKUP multiple sheets formula, the VLOOKUP function does the following:

Search for a value (the lookup value) down (vertically) the first/leftmost “column” in an array (a table).
Return a value on the same “row” (as the lookup value) but a different “column” you specify.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Specify VLOOKUP's arguments as follows when creating a VLOOKUP multiple sheets formula:

Lookup_value: The lookup value you specified in step #4.
Table_array: The INDIRECT function you created in step #8.
Col_index_num: The number of the column in the table (cell range) where you look in (as identified in step #8) with the value to return.
- The first column in the table is column 1.
- The second column in the table is column 2.
- …
- The nth column in the table is column n.
Range_lookup: FALSE.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
=VLOOKUP(LookupValue,IndirectFunctionFromStep8,NumberOfColumnWithValueToReturn,FALSE)

Considering the INDIRECT function you created in step #8:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
=VLOOKUP(LookupValue,INDIRECT("'"&INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'"&MultipleSheetList&"'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"'!AddressOfTableYouLookIn"),NumberOfColumnWithValueToReturn,FALSE)

The VLOOKUP function is case insensitive.
If the VLOOKUP function fails to find an exact match for the lookup value in the applicable table (cell range), the VLOOKUP function returns the #N/A error.

Step 9 Example

I specify VLOOKUP's arguments as follows:

(1) Lookup_value (using absolute references): $C$7.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
=VLOOKUP($C$7,

(2) Table_array: The array returned by the INDIRECT function I created in step #8.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),

(3) Col_index_num:

Cell C8: A8.
Cell C9: A9.
Cell C10: A10.
Cell C11: A11.
Cell C12: A12.
Cell C13: A13.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/

'Cell C8
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A8,

'Cell C9
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A9,

'Cell C10
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A10,

'Cell C11
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A11,

'Cell C12
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A12,

'Cell C13
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A13,

(4) Range_lookup: FALSE.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/excel-vlookup-multiple-sheets/

'Cell C8
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A8,FALSE)

'Cell C9
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A9,FALSE)

'Cell C10
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A10,FALSE)

'Cell C11
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A11,FALSE)

'Cell C12
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A12,FALSE)

'Cell C13
=VLOOKUP($C$7,INDIRECT("'"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A13,FALSE)

The results returned by the VLOOKUP multiple sheets example formulas (above) correspond to the data in row 25 of the “VLookup Sheet 3” worksheet (below).

Example data match for VLOOKUP multiple sheets

Step 10: Enter the Formula as an Array Formula

The VLOOKUP multiple sheets formula template/structure you learned in this Tutorial is an array formula.

If you're working with Excel 2019 or earlier, enter this VLOOKUP multiple sheets formula by pressing “Ctrl + Shift + Enter”. In these cases, Excel wraps the formula in curly braces ({}).

Step 10 Example

The image below displays the VLOOKUP multiple sheets example formulas wrapped in curly braces ({}) if I press “Ctrl + Shift + Enter” to enter them. The results are the same as those displayed in step #9.

VLOOKUP multiple sheets array formula example

Download the VLOOKUP Multiple Sheets Example Workbook

This Excel VLOOKUP Multiple Sheets Tutorial is accompanied by an Excel workbook with the data and formulas I use when describing the step-by-step process above. Get this example workbook (for free) by clicking the button below.

This Excel VLOOKUP Multiple Sheets Tutorial is part of a more comprehensive series of Excel VLookup Tutorials.

Excel VLOOKUP Tutorial (under development): Click here to open.
Excel VLOOKUP from Another Sheet in 4 Easy Steps: Click here to open.
Excel VLOOKUP Compare 2 Columns and Find Matches in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Row Values (in Same Column) in 4 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Columns in 5 Easy Steps: Click here to open.
Excel VLOOKUP Sum Multiple Columns (Values) in 6 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Column Values (with XLOOKUP) in 4 Easy Steps: Click here to open.
Excel VLookup Sum Multiple Rows and Columns in 3 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with INDEX MATCH in 4 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with XLOOKUP in 2 Easy Steps: Click here to open.
Excel VLookup Multiple Criteria with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLOOKUP Return Multiple Values with Helper Column in 4 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the INDEX Function in 7 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values with the FILTER Function in 2 Easy Steps: Click here to open.
Excel VLookup Return Multiple Values in One Cell Separated by a Comma in 4 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Sheets in Different Workbook in 10 Easy Steps: Click here to open.
Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps: Click here to open.

My Excel XLOOKUP Tutorial (click here to open) may help you:

Better understand and implement the contents above.
Better understand the differences between the XLOOKUP and VLOOKUP functions (XLOOKUP vs. VLOOKUP).

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to visit the Archives. The following are some of my most popular Excel Tutorials:

Excel Macro Tutorial for Beginners: Click here to open.
Excel Power Query (Get & Transform) Tutorial for Beginners: Click here to open.
350 + Excel Keyboard Shortcuts And Hotkeys: Click here to open.

Excel essentials and must-know skills.
Advanced Excel Formulas.
Excel Tables.
Pivot Tables.
Dashboards.
Power Pivot.
Power Query.

If you want to start learning how to automate Excel (and save time) by working with macros and VBA, you may be interested in the following Premium Excel Macro and VBA Training Materials:

Premium Courses at the Power Spreadsheets Academy: Click here to open.
Books at the Power Spreadsheets Library: Click here to open.