Excel VLOOKUP Multiple Sheets in Different Workbook in 10 Easy Steps

In this Excel VLOOKUP multiple sheets in different workbook Tutorial, you learn how to carry out a VLOOKUP across multiple sheets stored in a different workbook.

This Excel VLOOKUP multiple sheets in different workbook Tutorial is accompanied by 2 Excel workbooks with the data and formulas I use when describing the step-by-step process below. You can get these example workbooks (for free) by clicking the button below.

Table of Contents

Related Excel VLookup Training Materials and Resources

This Excel VLOOKUP multiple sheets in different workbook 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 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 10 Easy Steps: Click here to open.
Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps: Click here to open.

You can find more Excel Tutorials in the organized Tutorials Archive: 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 in Different Workbook Formula Template/Structure

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

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

This is an array formula. If you're not working with Excel 365, you may have to 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/vlookup-multiple-sheets-workbook/
{=VLOOKUP(LookupValue,INDIRECT("'[WorkbookName]"&INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'[WorkbookName]"&MultipleSheetList&"'!AddressOfTableYouLookIn"),LookupValue)>0),0))&"'!AddressOfTableYouLookIn"), NumberOfColumnWithValueToReturn,FALSE)}

The Example Before VLOOKUP Multiple Sheets in Different Workbook

I describe the main characteristics of the 2 workbooks that accompany this Excel VLOOKUP multiple sheets in different workbook Tutorial below.

(1) Data Workbook (VLookup Multiple Sheets Different Workbook.xlsx).

A workbook with 3 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 in different workbook 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 in different workbook 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.1) VLookup Sheet 1.

Workbook data 1 for VLOOKUP multiple sheets different workbook

(1.2) VLookup Sheet 2.

Workbook data 2 for VLOOKUP multiple sheets different workbook

(1.3) VLookup Sheet 3.

Workbook data 3 for VLOOKUP multiple sheets different workbook

(2) Example Workbook.

The Before worksheet in the Example Workbook has a single table (cells A6 to D13). This is the table where you:

Set up the VLOOKUP multiple sheets in different workbook 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 multiple sheets (VLookup Sheet 1, VLookup Sheet 2, VLookup Sheet 3; the tables the VLOOKUP multiple sheets in different workbook formulas work with) of the Data Workbook (“VLookup Multiple Sheets Different Workbook.xlsx”).
Header: The values in cells B7 to B13 match the headers of the tables in the sheets of the Data Workbook (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 in different workbook formulas.
Formula: Cells D8 to D13:
- Currently display the #N/A error.
- Will display the VLOOKUP multiple sheets in different workbook formulas I enter in cells C8 to C13.

VLOOKUP multiple sheets different workbook before formulas

Step 1: Create List of Multiple Sheets

Create a list with the names of the multiple sheets (in the different workbook) where you carry out the VLOOKUP multiple sheets in different workbook.

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.

The VLOOKUP multiple sheets in different workbook formula template/structure you learn in this Tutorial assumes the different workbook is open. This is required by the INDIRECT function (you use in steps #3 and #8).

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 the different workbook in column F (cells F6 to F9) of the worksheet where I set up the VLOOKUP multiple sheets in different workbook 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 Inside the Different Workbook) You Look In

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

The following 2 items, wrapped in single quotes (‘[WorkbookName]WorksheetName'):
1. The workbook name, wrapped in square brackets ([WorkbookName])
2. The worksheet name (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 4 items:
1. A single quote (‘).
2. An opening square bracket ([).
3. The name of the different workbook (for example, entered as text) with the multiple sheets.
4. A closing square bracket (]).
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/vlookup-multiple-sheets-workbook/
"'[WorkbookName]"&MultipleSheetList&"'!AddressOfTableYouLookIn"

Step 2 Example

I look in column A (cells A9 to A28) of the multiple sheets inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook. I use the ampersand (&) text concatenation operator to concatenate the following 3 items:

A string with 4 items (“‘[VLookup Multiple Sheets Different Workbook.xlsx]”):
1. A single quote: ‘
2. An opening square bracket: [.
3. The name of the workbook with the multiple sheets: VLookup Multiple Sheets Different Workbook.xlsx.
4. A closing square bracket: ].
An absolute reference to the multiple sheet list I created in step #1: $F$7:$F$9.
A string with 3 items (“‘!$A$9:$A$28”):
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/vlookup-multiple-sheets-workbook/
"'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"

The image below displays how this expression returns an array with fully qualified text references (single quote, workbook name wrapped in square brackets, worksheet name, single quote, exclamation sign, column address) to the column I look in (in each of the multiple sheets inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook).

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 (=”‘[VLookup Multiple Sheets Different Workbook.xlsx]”&$F$7:$F$9&”‘!$A$9:$A$28″) stored in column G.

References to column you look in for VLOOKUP multiple sheets different workbook

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

Use the INDIRECT function to obtain the values stored in each column (in each of the multiple sheets inside the different workbook) 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/vlookup-multiple-sheets-workbook/
INDIRECT(ref_text)

To obtain the values stored in each column (in each of the multiple sheets inside the different workbook) you look in when creating a VLOOKUP multiple sheets in different workbook 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 inside the different workbook) you look in.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-sheets-workbook/
INDIRECT("'[WorkbookName]"&MultipleSheetList&"'!AddressOfTableYouLookIn")

Step 3 Example

Considering the text references I created in step #2 to cells A9 to A28 in each of the multiple sheets (VLookup Sheet 1, VLookup Sheet 2, VLookup Sheet 3) I look in inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-sheets-workbook/
INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$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 inside the different workbook) I look in.

Columns I to K (“VLookup Sheet 1” Different Workbook INDIRECT Output, “VLookup Sheet 2” Different Workbook INDIRECT Output, “VLookup Sheet 3” Different Workbook INDIRECT Output) display the values stored in each column in each of the multiple sheets I look in (inside the different workbook).
- Column I (cells I9 to I28) displays the values stored in column A (cells A9 to A28) of the “VLookup Sheet 1” worksheet inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.
- Column J (cells J9 to J28) displays the values stored in column A (cells A9 to A28) of the “VLookup Sheet 2” worksheet inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.
- Column K (cells K9 to K28) displays the values stored in column A (cells A9 to A28) of the “VLookup Sheet 3” worksheet inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.
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(“‘[VLookup Multiple Sheets Different Workbook.xlsx]”&$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 Stored in the Different Workbook

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 (included in the multiple sheet list you created in step #1) stored in the different workbook.

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/vlookup-multiple-sheets-workbook/
COUNTIF(range,criteria)

To count the number of times the lookup value appears in the column you look in (in each of the applicable sheets inside the different workbook) when creating a VLOOKUP multiple sheets in different workbook 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 inside the different workbook) specified in step #2.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-sheets-workbook/
COUNTIF(INDIRECT("'[WorkbookName]"&MultipleSheetList&"'!AddressOfTableYouLookIn"),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).

Considering the INDIRECT function I created in step #3:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-sheets-workbook/
COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$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 stored in the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.

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) stored in the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.
Cell H7 displays the formula (=COUNTIF(INDIRECT(“‘[VLookup Multiple Sheets Different Workbook.xlsx]”&$F$7:$F$9&”‘!$A$9:$A$28”),$C$7)) stored in column G.

Count lookup value appearances for VLOOKUP multiple sheets different workbook

The lookup value (Salesperson 7):

Doesn't appear (Lookup Value Count is 0) in:
- The “VLookup Sheet 1” worksheet inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.
- The “VLookup Sheet 2” worksheet inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.
Appears 1 time (Lookup Value Count is 1) in the “VLookup Sheet 3” worksheet inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.

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

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 stored in the different workbook).

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 stored in the different workbook.

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-sheets-workbook/
COUNTIF(INDIRECT("'[WorkbookName]"&MultipleSheetList&"'!AddressOfTableYouLookIn"),LookupValue)>0

Step 5 Example

Considering the COUNTIF function I created in step #4:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-sheets-workbook/
COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$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 stored in the “VLookup Multiple Sheets Different Workbook.xlsx” workbook).

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) stored in the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.
- FALSE: The lookup value stored in cell C7 (Salesperson 7) doesn't appear in column A inside the applicable sheet (listed in column F) stored in the “VLookup Multiple Sheets Different Workbook.xlsx” workbook.
Cell H7 displays the formula (=COUNTIF(INDIRECT(“‘[VLookup Multiple Sheets Different Workbook.xlsx]”&$F$7:$F$9&”‘!$A$9:$A$28”),$C$7)>0) stored in column G.

Test lookup value appearance in workbook for VLOOKUP sheet multiple different workbooks

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

Use the MATCH function to identify the relative position:

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

In the VLOOKUP multiple sheets in different workbook 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/vlookup-multiple-sheets-workbook/
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/vlookup-multiple-sheets-workbook/
MATCH(1,--(ConditionalTestFromStep5),0)

When considering the conditional test you created in step #5:

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

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

As a general rule: If the lookup value is found in the applicable column inside more than 1 sheet (in the different workbook):
- 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 worksheet (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 worksheets (in the different workbook):
- 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/vlookup-multiple-sheets-workbook/
MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0)

Position of first worksheet in different workbook where lookup value appears

The lookup value (Salesperson 7) appears in the third worksheet inside the multiple sheet list. This is the “VLookup Sheet 3” worksheet inside the “VLookup Multiple Sheets Different Workbook.xlsx”.

Step 7: Obtain the Name of the Sheet (in the Different Workbook) 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 worksheet (inside the different workbook) where the lookup value appears (in the applicable column).

In the VLOOKUP multiple sheets in different workbook 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/vlookup-multiple-sheets-workbook/
INDEX(reference,row_num)

To obtain the name of the worksheet (inside the different workbook) 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/vlookup-multiple-sheets-workbook/
INDEX(MultipleSheetList,MatchFunctionFromStep6)

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-sheets-workbook/
INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'[WorkbookName]"&MultipleSheetList&"'!AddressOfTableYouLookIn"),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/vlookup-multiple-sheets-workbook/
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/vlookup-multiple-sheets-workbook/
INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))

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

The name of the worksheet (inside the “VLookup Multiple Sheets Different Workbook.xlsx” workbook) 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 (Stored in the Different Workbook) 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/vlookup-multiple-sheets-workbook/
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 in different workbook formula:

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

A string with the following 4 items:
1. A single quote (‘).
2. An opening square bracket ([).
3. The name of the different workbook (for example, entered as text) with the multiple sheets.
4. A closing square bracket (]).
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/vlookup-multiple-sheets-workbook/
"'[WorkbookName]"&INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'[WorkbookName]"&MultipleSheetList&"'!AddressOfTableYouLookIn"),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/vlookup-multiple-sheets-workbook/
INDIRECT("'[WorkbookName]"&INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'[WorkbookName]"&MultipleSheetList&"'!AddressOfTableYouLookIn"),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 4 items:
1. A single quote: ‘.
2. An opening square bracket: [.
3. The name of the different workbook: VLookup Multiple Sheets Different Workbook.xlsx.
4. A closing square bracket: ].
The INDEX function I created in step #7: INDEX($F$7:$F$9,MATCH(1,–(COUNTIF(INDIRECT(“‘[VLookup Multiple Sheets Different Workbook.xlsx]”&$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/vlookup-multiple-sheets-workbook/
INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$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(“‘[VLookup Multiple Sheets Different Workbook.xlsx]”&INDEX($F$7:$F$9,MATCH(1,–(COUNTIF(INDIRECT(“‘[VLookup Multiple Sheets Different Workbook.xlsx]”&$F$7:$F$9&”‘!$A$9:$A$28″),$C$7)>0),0))&”‘!$A$9:$G$28”)) stored in cells H8 to N27.

Data array for VLOOKUP multiple sheets different workbook

Step 9: Carry out the VLookup

Work with the VLOOKUP function to carry out the VLOOKUP multiple sheets in different workbook.

In the VLOOKUP multiple sheets in different workbook 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/vlookup-multiple-sheets-workbook/
VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Specify VLOOKUP's arguments as follows when creating a VLOOKUP multiple sheets in different workbook 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/vlookup-multiple-sheets-workbook/
=VLOOKUP(LookupValue,IndirectFunctionFromStep8,NumberOfColumnWithValueToReturn,FALSE)

Considering the INDIRECT function you created in step #8:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-sheets-workbook/
=VLOOKUP(LookupValue,INDIRECT("'[WorkbookName]"&INDEX(MultipleSheetList,MATCH(1,--(COUNTIF(INDIRECT("'[WorkbookName]"&MultipleSheetList&"'!AddressOfTableYouLookIn"),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/vlookup-multiple-sheets-workbook/
=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/vlookup-multiple-sheets-workbook/
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$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/vlookup-multiple-sheets-workbook/

'Cell C8
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A8,

'Cell C9
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A9,

'Cell C10
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A10,

'Cell C11
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A11,

'Cell C12
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A12,

'Cell C13
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$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/vlookup-multiple-sheets-workbook/

'Cell C8
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A8,FALSE)

'Cell C9
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A9,FALSE)

'Cell C10
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A10,FALSE)

'Cell C11
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A11,FALSE)

'Cell C12
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A12,FALSE)

'Cell C13
=VLOOKUP($C$7,INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'[VLookup Multiple Sheets Different Workbook.xlsx]"&$F$7:$F$9&"'!$A$9:$A$28"),$C$7)>0),0))&"'!$A$9:$G$28"),A13,FALSE)

VLOOKUP multiple sheets different workbook formula example

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

Example data match for VLOOKUP multiple sheets different workbook

Step 10: Enter the Formula as an Array Formula

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

If you're not working with Excel 365, you may have to enter this VLOOKUP multiple sheets in different workbook 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 in different workbook 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 different workbook array formula example

Download the VLOOKUP Multiple Sheets in Different Workbook Example Workbooks

This Excel VLOOKUP multiple sheets in different workbook Tutorial is accompanied by 2 Excel workbooks with the data and formulas I use when describing the step-by-step process above. You can get these example workbooks (for free) by clicking the button below.

Related Excel VLookup Training Materials and Resources

This Excel VLOOKUP multiple sheets in different workbook 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 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 10 Easy Steps: Click here to open.
Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps: Click here to open.

You can find more Excel Tutorials in the organized Tutorials Archive: Click here to open.