Excel VLOOKUP Sheet in Multiple Different Workbooks in 10 Easy Steps

In this Excel VLOOKUP Sheet in Multiple Different Workbooks Tutorial, you learn how to carry out a VLOOKUP across sheets stored in multiple different workbooks.

The VLOOKUP sheet in multiple different workbooks 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.
Excel VLOOKUP Multiple Sheets in Different Workbook.

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

This Excel VLOOKUP Sheet in Multiple Different Workbooks Tutorial is accompanied by several Excel workbooks with the data and formulas I use when describing the step-by-step process below. Get these example workbooks (for free) by clicking the button below.

Table of Contents

This Excel VLOOKUP Sheet in Multiple Different Workbooks 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 10 Easy Steps: Click here to open.
Excel VLOOKUP Multiple Sheets in Different Workbook 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 Sheet in Multiple Different Workbooks Formula Template/Structure

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-different-workbooks/
=VLOOKUP(LookupValue,INDIRECT("'["&INDEX(MultipleWorkbookList,MATCH(1,--(COUNTIF(INDIRECT("'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"]SheetName'!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/vlookup-multiple-different-workbooks/
{=VLOOKUP(LookupValue,INDIRECT("'["&INDEX(MultipleWorkbookList,MATCH(1,--(COUNTIF(INDIRECT("'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"]SheetName'!AddressOfTableYouLookIn"),NumberOfColumnWithValueToReturn,FALSE)}

The Example Before VLOOKUP Sheet in Multiple Different Workbooks

The 4 workbooks that accompany this Excel VLOOKUP Sheet in Multiple Different Workbooks Tutorial can be classified in 2 categories:

3 workbooks (the Data Workbooks):
- Store the data.
- Are the workbooks (each workbook containing a worksheet) where you search with the VLOOKUP sheet in multiple different workbooks example formula.
1 workbook (the Example Workbook) is the workbook where you:
- Set up the VLOOKUP sheet in multiple different workbooks example formulas.
- Display the results.

I describe the main characteristics of these 4 workbooks below.

(1) Data Workbooks (VLookup Multiple Different Workbooks 1.xlsm, VLookup Multiple Different Workbooks 2.xlsm, VLookup Multiple Different Workbooks 3.xlsm).

Each workbook has a single sheet (VLookup Sheet) with a single table (cells A8 to G28).

The tables' layout is the same (across Data Workbooks). The VLOOKUP sheet in multiple different workbooks formula template/structure you learn in this Tutorial assumes the tables' layout remains the same (across workbooks).

These are the tables:

With the data.
Where you search with the VLOOKUP sheet in multiple different workbooks 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.1) VLookup Multiple Different Workbooks 1.xlsx.

Workbook data 1 for VLOOKUP multiple workbooks

(1.2) VLookup Multiple Different Workbooks 2.xlsx.

Workbook data 2 for VLOOKUP multiple workbooks

(1.3) VLookup Multiple Different Workbooks 3.xlsx.

Workbook data 3 for VLOOKUP multiple workbooks

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

VLOOKUP sheet multiple different workbooks before formulas

Step 1: Create List of Multiple Different Workbooks

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

Consider, for example:

Entering the multiple different workbook list in a worksheet column; and
Naming the applicable cell range by, for example:
1. Selecting the cell range containing the multiple different workbook list.
  - Select (only) the multiple different workbook 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 different workbook list).
  3. Click the OK button.

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

Step 1 Example

I work with 3 workbooks:

VLookup Multiple Different Workbooks 1.xlsx.
VLookup Multiple Different Workbooks 2.xlsx.
VLookup Multiple Different Workbooks 3.xlsx.

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

Cell F6 is a header (VLookup Multiple Different Workbook List).
Cells F7 to F9 contain the multiple different workbooks' names.

Multiple different workbook list for VLOOKUP

Step 2: Create an Array with Text References to the Column (in the Applicable Sheet Inside the Multiple Different Workbooks) You Look In

Create an array with text references to the column (in the applicable sheet inside each of the multiple different workbooks) 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 a single quote and an opening square bracket (“‘[“).
A reference to the multiple different workbook list you created in step #1.
A string with 5 items:
1. A closing square bracket (]).
2. The name of the sheet (for example, entered as text) inside the multiple different workbooks.
3. A single quote (‘).
4. An exclamation sign (!).
5. 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-different-workbooks/
"'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn"

Step 2 Example

I look in column A (cells A9 to A28) of the sheet (VLookup Sheet) inside each of the multiple different workbooks. I use the ampersand (&) text concatenation operator to concatenate the following 3 items:

A string with a single quote and an opening square bracket: “‘[“.
An absolute reference to the multiple different workbook list I created in step #1: $F$7:$F$9.
A string with 5 items (“]VLookup Sheet'!$A$9:$A$28”):
1. A closing square bracket: ].
2. The name of the sheet inside the multiple different workbooks: VLookup Sheet.
3. A single quote: ‘.
4. An exclamation sign: !.
5. 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-different-workbooks/
"'["&$F$7:$F$9&"]VLookup Sheet'!$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 (in the “VLookup Sheet” sheet inside each of the multiple different workbooks) I look in.

Column F (VLookup Multiple Different Workbook List) contains the multiple different workbook 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&”]VLookup Sheet'!$A$9:$A$28″) stored in column G.

References to column you look in for VLOOKUP sheet multiple different workbooks

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

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

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-different-workbooks/
INDIRECT("'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn")

Step 3 Example

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-different-workbooks/
INDIRECT("'["&$F$7:$F$9&"]VLookup Sheet'!$A$9:$A$28")

The image below illustrates how this expression returns the values stored in each column (in the “VLookup Sheet” sheet inside each of the multiple different workbooks) I look in.

Columns I to K (“VLookup Multiple Different Workbooks 1” INDIRECT Output, “VLookup Multiple Different Workbooks 2” INDIRECT Output, “VLookup Multiple Different Workbooks 3” INDIRECT Output) display the values stored in each column in the “VLookup Sheet” sheet inside each of the multiple different workbooks I look in.
- Column I (cells I9 to I28) displays the values stored in column A (cells A9 to A28) of the “VLookup Sheet” worksheet inside the “VLookup Multiple Different Workbooks 1.xlsx” workbook.
- Column J (cells J9 to J28) displays the values stored in column A (cells A9 to A28) of the “VLookup Sheet” worksheet inside the “VLookup Multiple Different Workbooks 2.xlsx” workbook.
- Column K (cells K9 to K28) displays the values stored in column A (cells A9 to A28) of the “VLookup Sheet” worksheet inside the “VLookup Multiple Different Workbooks 3.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(“‘[“&$F$7:$F$9&”]VLookup Sheet'!$A$9:$A$28″)).
- But (for illustrative purposes in this Tutorial) refer to a single cell in the multiple different workbook 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 the Applicable Sheet Stored in Each of the Multiple Different Workbooks

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) inside the applicable sheet;
Stored in each of the multiple different workbooks you work with (and included in the multiple different workbook 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/vlookup-multiple-different-workbooks/
COUNTIF(range,criteria)

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-different-workbooks/
COUNTIF(INDIRECT("'["&MultipleWorkbookList&"]SheetName'!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/vlookup-multiple-different-workbooks/
COUNTIF(INDIRECT("'["&$F$7:$F$9&"]VLookup Sheet'!$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 “VLookup Sheet” sheet stored in the applicable workbook.

Column F (VLookup Multiple Different Workbook List) contains the multiple different workbook 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 “VLookup Sheet” sheet stored in the applicable workbook (listed in column F).
Cell H7 displays the formula (=COUNTIF(INDIRECT(“‘[“&$F$7:$F$9&”]VLookup Sheet'!$A$9:$A$28″),$C$7)) stored in column G.

Count lookup value appearances for VLOOKUP sheet multiple different workbooks

The lookup value (Salesperson 7):

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

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

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

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 the applicable sheet stored in each of the multiple different workbooks.

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-different-workbooks/
COUNTIF(INDIRECT("'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn"),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-different-workbooks/
COUNTIF(INDIRECT("'["&$F$7:$F$9&"]VLookup Sheet'!$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 the “VLookup Sheet” sheet stored in each of the multiple different workbooks).

Column F (VLookup Multiple Different Workbook List) contains the multiple different workbook 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 “VLookup Sheet” sheet stored in the applicable workbook (listed in column F).
- FALSE: The lookup value stored in cell C7 (Salesperson 7) doesn't appear in column A inside the “VLookup Sheet” sheet stored in the applicable workbook (listed in column F).
Cell H7 displays the formula (=COUNTIF(INDIRECT(“‘[“&$F$7:$F$9&”]VLookup Sheet'!$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 Different Workbook List) of the First Workbook Where the Lookup Value Appears (in the Applicable Sheet and Column)

Use the MATCH function to identify the relative position:

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

In the VLOOKUP sheet in multiple different workbooks 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-different-workbooks/
MATCH(lookup_value,lookup_array,match_type)

To find the relative position of the first workbook (inside the multiple different workbook list) where the lookup value appears (in the applicable sheet and 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-different-workbooks/
MATCH(1,--(ConditionalTestFromStep5),0)

Considering the conditional test you created in step #5:

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-different-workbooks/
MATCH(1,--(COUNTIF(INDIRECT("'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn"),LookupValue)>0),0)

Consider the following when setting up the MATCH function inside the VLOOKUP sheet in multiple different workbooks formula:

As a general rule: If the lookup value is found in the applicable column inside more than 1 workbook 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 workbook (inside the multiple different workbook list) where the lookup value appears (in the applicable sheet and column).
If the lookup value isn't found in the applicable sheet and column inside any of the workbooks:
- 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-different-workbooks/
MATCH(1,--(COUNTIF(INDIRECT("'["&$F$7:$F$9&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0)

Position of first workbook where lookup value appears for VLOOKUP sheet multiple different workbooks

The lookup value (Salesperson 7) appears in the third workbook inside the multiple different workbook list. This is the “VLookup Multiple Different Workbooks 3.xlsx” workbook.

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

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

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

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

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

Reference: A reference to the multiple different workbook 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-different-workbooks/
INDEX(MultipleWorkbookList,MatchFunctionFromStep6)

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-different-workbooks/
INDEX(MultipleWorkbookList,MATCH(1,--(COUNTIF(INDIRECT("'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn"),LookupValue)>0),0))

Step 7 Example

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

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-different-workbooks/
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-different-workbooks/
INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'["&$F$7:$F$9&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0))

Name of workbook where lookup value first appears for VLOOKUP sheet multiple different workbooks

The name of the workbook where the lookup value (Salesperson 7) appears is “VLookup Multiple Different Workbooks 3.xlsx”.

Step 8: Obtain an Array with the Values Stored in the Table Where You Look In Inside the Applicable Sheet Stored in the 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 applicable sheet stored in the workbook 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-different-workbooks/
INDIRECT(ref_text)

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

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

A string with a single quote and an opening bracket (“‘[“).
The INDEX function you created in step #7.
A string with the following 5 items:
1. A closing square bracket (]).
2. The name of the sheet (for example, entered as text) inside the multiple different workbooks.
3. A single quote (‘).
4. An exclamation sign (!).
5. 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-different-workbooks/
"'["&INDEX(MultipleWorkbookList,MATCH(1,--(COUNTIF(INDIRECT("'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"]SheetName'!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-different-workbooks/
INDIRECT("'["&INDEX(MultipleWorkbookList,MATCH(1,--(COUNTIF(INDIRECT("'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"]SheetName'!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 and an opening square bracket: “‘[“.
The INDEX function I created in step #7: INDEX($F$7:$F$9,MATCH(1,–(COUNTIF(INDIRECT(“‘[“&$F$7:$F$9&”]VLookup Sheet'!$A$9:$A$28″),$C$7)>0),0)).
A string with 5 items (“]VLookup Sheet'!$A$9:$G$28”).
1. A closing square bracket: ].
2. The name of the sheet inside the multiple different workbooks: VLookup Sheet.
3. A single quote: ‘.
4. An exclamation sign: !.
5. 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-different-workbooks/
INDIRECT("'["&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'["&$F$7:$F$9&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0))&"]VLookup Sheet'!$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 “VLookup Sheet” worksheet stored in the workbook 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&”]VLookup Sheet'!$A$9:$A$28″),$C$7)>0),0))&”]VLookup Sheet'!$A$9:$G$28″)) stored in cells H8 to N27.

Data array for VLOOKUP sheet multiple different workbooks

Step 9: Carry out the VLookup

Work with the VLOOKUP function to carry out the VLOOKUP sheet in multiple different workbooks.

In the VLOOKUP sheet in multiple different workbooks 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-different-workbooks/
=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup)

Specify VLOOKUP's arguments as follows when creating a VLOOKUP sheet in multiple different workbooks 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-different-workbooks/
=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-different-workbooks/
=VLOOKUP(LookupValue,INDIRECT("'["&INDEX(MultipleWorkbookList,MATCH(1,--(COUNTIF(INDIRECT("'["&MultipleWorkbookList&"]SheetName'!AddressOfColumnYouLookIn"),LookupValue)>0),0))&"]SheetName'!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-different-workbooks/
=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-different-workbooks/
=VLOOKUP($C$7,INDIRECT("'["&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'["&$F$7:$F$9&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0))&"]VLookup Sheet'!$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-different-workbooks/

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

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

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

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

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

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

(4) Range_lookup: FALSE.

'Source: https://powerspreadsheets.com/
'More information: https://powerspreadsheets.com/vlookup-multiple-different-workbooks/

'Cell C8
=VLOOKUP($C$7,INDIRECT("'["&INDEX($F$7:$F$9,MATCH(1,--(COUNTIF(INDIRECT("'["&$F$7:$F$9&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0))&"]VLookup Sheet'!$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&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0))&"]VLookup Sheet'!$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&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0))&"]VLookup Sheet'!$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&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0))&"]VLookup Sheet'!$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&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0))&"]VLookup Sheet'!$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&"]VLookup Sheet'!$A$9:$A$28"),$C$7)>0),0))&"]VLookup Sheet'!$A$9:$G$28"),A13,FALSE)

VLOOKUP sheet multiple different workbooks formula example

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

Example data match for VLOOKUP sheet multiple different workbooks

Step 10: Enter the Formula as an Array Formula

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

If you're working with Excel 2019 or earlier, enter this VLOOKUP sheet in multiple different workbooks 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 sheet in multiple different workbooks 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 sheet multiple different workbooks array formula example

Download the VLOOKUP Sheet in Multiple Different Workbooks Example Workbooks

This Excel VLOOKUP Sheet in Multiple Different Workbooks Tutorial is accompanied by several Excel workbooks with the data and formulas I use when describing the step-by-step process above. Get these example workbooks (for free) by clicking the button below.

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