How to Lookup Values Across Multiple Sheets in Excel (3 Methods)

Free Excel workbook

Download the Lookup Practice File

Download the exact workbook from the tutorial.

Get the Workbook

No spam. Unsubscribe anytime.

Let’s say you have a main Excel sheet called “Summary.” This sheet helps you look at specific information from other sheets within the same file.

For example, you might want to see invoiced amounts from different departments. These include the Game, Productivity, and Utility divisions.

In addition, you want to have the option to choose a month.

Here’s how you can set it up simply:

The “Summary” sheet includes a cell (B4) where you select a month.
Each division has its own sheet named accordingly: Game Div., Productivity Div., and Utility Div.
These division sheets are organized the same way. They list invoiced amounts month by month.

Your goal is to lookup the invoiced amounts from the relevant division sheet based on the month you’ve selected in the Summary sheet. This setup allows you to view specific financial details quickly and easily.

Why Shouldn’t You Use Nested IF for Cross-Sheet Lookups?

The IF function can help you lookup data from specific tabs based on conditions you set. For example, if you’re looking at divisional data, you might set up the function to get information from the “Game Div” tab when the division is “Game Div,” and similarly for other divisions.

However, this method has a drawback. Every time you add a new tab, you need to update all the IF statements in your summary sheet. This can become cumbersome and prone to errors as your Excel file grows.

A better approach would be to use a formula that automatically adjusts to include any new tabs you add. This makes your Excel sheet easier to manage and more dynamic, accommodating changes without needing constant updates.

How Do You VLOOKUP Across Multiple Sheets with INDIRECT?

To manage data from multiple sheets effectively, you can start with the basic VLOOKUP formula.

Once the basic formula is set up, we will cover how to do a VLOOKUP between two sheets. Learn more about VLOOKUP in this detailed guide.

How Does a Basic Cross-Sheet VLOOKUP Work?

• Begin in cell C6.
• Use this basic syntax for VLOOKUP:

= VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: This is what you’re searching for. For example, the month is listed in cell B4. Make sure to fix this reference to avoid changes as you move the formula around: use $B$4.
• table_array: This is where you find the data. Start with the Game Division tab like this: Game Div.!$A$4:$B$24.
• col_index_num: Choose the column number that has the data you need; here it’s 2 for the second column.
• range_lookup: Set this to FALSE to ensure you only get exact matches.

Example formula for cell C6:

• lookup_value: Since you know that you will be looking at the Game Div. tab, this does not need to be an argument. Lookup value is the cell containing the month, cell B4. Since you want to be able to pull this formula down, fix this cell reference to $B$4.
• table_array: Go to Game Div. and highlight the entire table and add a few more rows to include future data (‘Game Div.’!$A$4:$B$24)
• col_index_num: This tells which column to look at. In this case, since we want the second column in the table array area, we use 2.
• range_lookup: Select TRUE for an approximate match or false for an exact match. In this example, we want an exact match.

= VLOOKUP($B$4, 'Game Div.'!$A$4:$B$24, 2, FALSE)

How Does INDIRECT Make VLOOKUP Dynamic Across Sheets?

If you copy the formula to different rows, you’ll want it to pull data from various division sheets, not just from one. Let’s look at a VLOOKUP example between two sheets.

The INDIRECT function is perfect for this. It allows you to use text as a reference in your formulas.

Using INDIRECT to Switch Sheets Dynamically:

Purpose of INDIRECT: It converts text into a usable Excel reference. For instance, if you write the division name in a cell, INDIRECT can use that cell’s text to refer to a specific sheet.

How to Use INDIRECT:

• Say cell B6 contains the name of the division, like “Game Div.”.
• Normally, your VLOOKUP formula looks directly at a specific sheet:

= VLOOKUP($B$4, 'Game Div.'!$A$4:$B$24, 2, FALSE)

• To make it dynamic, use INDIRECT to tell Excel to get the sheet name from cell B6 instead of it being fixed in the formula.

Dynamic VLOOKUP Formula:

= VLOOKUP($B$4, INDIRECT("'" & B6 & "'!$A$4:$B$24"), 2, FALSE)

Here’s what’s happening:

• INDIRECT(“‘” & B6 & “‘!$A$4:$B$24”) is the key part. It builds the sheet reference from the text in B6.
• ‘&’ connects pieces of text in Excel, making a complete reference that VLOOKUP can use.
• $B$4 is the cell with the month you’re looking up.
• 2 tells VLOOKUP to look in the second column for the amount.
• FALSE specifies you need an exact match.

Now, whenever you change the division name in B6, the formula automatically updates to pull data from the new division’s sheet.

Just copy this formula down the rows, and it will work for any division listed in column B.

If you want to learn more about how the INDIRECT function can make your Excel work more flexible and powerful, feel free to check out our detailed article.

How Do You Use INDEX MATCH to Lookup Data Across Sheets?

Another option is to use INDEX MATCH for a lookup across multiple sheets. This approach involves converting all the data in the Division tabs into Excel data tables.

Why Should You Convert Data to Tables First?

• Click on any data cell in a Division tab.
• Press CTRL + T to bring up the Create Table window.
• Confirm the data range and click OK to convert the data into an Excel table.

• Rename the table to avoid spaces, like changing “Game Div” to “Game_Div”. This makes referring to them in formulas easier.

Change Table Design (Optional):

• Click on any cell in your newly created table.
• Navigate to the Design tab, choose Table Styles to change its look, or Clear to revert to the original style.

How Do You Build the INDEX MATCH Formula?

Set Up Basic Formula:

Begin with the INDEX function:

= INDEX(array, row_num, [column_num])

• array: This is where your data is. For example, “Game_Div.[Invoiced Amount]”.
• row_num: Use the MATCH function here to find the right row. It searches for a specific date in the “Game_Div.[Date]” column and returns its position.

Combine INDEX with MATCH:

The MATCH function looks like this:

= MATCH(lookup_value, lookup_array, [match_type])

• lookup_value: This is what you’re searching for, e.g., a date in cell B4 on the Summary tab.
• lookup_array: The column that contains data to match the lookup value, like “Game_Div.[Date]”.
• match_type: Set this to 0 for an exact match.

Example Formula for One Sheet:

= INDEX(Game_Div.[Invoiced Amount], MATCH(Summary!$B$4, Game_Div.[Date], 0))

How Do You Make INDEX MATCH Dynamic with INDIRECT and SUBSTITUTE?

Dragging this formula down to the Utility Div. row will return the same values since they are hard-coded to look inside the Game Div. tab.

To fix this, we will use the INDIRECT function to help us get the dynamic tab names.

Dynamic Sheet Names with INDIRECT:

• Use INDIRECT to refer dynamically to different division tables based on cell B6 content.
• Replace spaces in names with underscores using the SUBSTITUTE function:

This is the syntax for SUBSTITUTE:

= SUBSTITUTE(text, old_text, new_text, [instance_num]).

• text: The cell you want the substitution to take place.
• old_text: What specific character you want to replace. In this case, it is “ “.
• new_text: What to substitute the old_text with. In this case, it is “_”.
• Instance_num: How many times we want the substitution to take place. This is an optional argument. We can leave it out which means we’d like all instances of “ “ to be replace with “_”.

In our case we just need to replace the spaces with and underscore: for example “Game Div” becomes “Game_Div” to match the Excel table name.

= SUBSTITUTE(B6, " ", "_")

Full Dynamic Formula:

= INDEX(INDIRECT(SUBSTITUTE(B6, " ", "") & "[Invoiced Amount]"), MATCH(Summary!$B$4, INDIRECT(SUBSTITUTE(B6, " ", "") & "[Date]"), 0))

Drag this formula down from cell D6 to D8 to apply it for other divisions.

If you want a deeper understanding of how to use the INDEX MATCH functions, check out our detailed guide.

Can You Use XLOOKUP Instead of VLOOKUP Across Multiple Sheets?

If you’re on Microsoft 365 or Excel 2021+, XLOOKUP is the better choice for cross-sheet lookups. It’s simpler to write, doesn’t need a column index number, and searches in any direction (not just left to right like VLOOKUP).

XLOOKUP with INDIRECT for Dynamic Sheet Names

The same INDIRECT technique from the VLOOKUP method above works with XLOOKUP. The syntax is actually cleaner because XLOOKUP separates the lookup array and return array into distinct arguments.

Here’s the static version, pulling from the Game Div. sheet:

=XLOOKUP($B$4, 'Game Div.'!$A$4:$A$24, 'Game Div.'!$B$4:$B$24)

To make it dynamic based on the division name in cell B6:

=XLOOKUP($B$4, INDIRECT("'" & B6 & "'!$A$4:$A$24"), INDIRECT("'" & B6 & "'!$B$4:$B$24"))

How it works:

$B$4 is the month you’re looking up. The first INDIRECT builds the lookup range (dates) on the target sheet. The second INDIRECT builds the return range (invoiced amounts) on the same sheet. When you copy the formula down, B6 changes to B7, B8, etc., pulling from different division sheets automatically.

No column index number to count. No need to remember whether the return column is the 2nd or 3rd. You point directly at the return range.

XLOOKUP with VSTACK to Search All Sheets at Once

If you want to search across every division sheet in a single formula without relying on INDIRECT, VSTACK is the modern approach. It stacks ranges from multiple sheets into one virtual array that XLOOKUP can search.

=XLOOKUP($B$4, VSTACK('Game Div.'!$A$4:$A$24, 'Productivity Div.'!$A$4:$A$24, 'Utility Div.'!$A$4:$A$24), VSTACK('Game Div.'!$B$4:$B$24, 'Productivity Div.'!$B$4:$B$24, 'Utility Div.'!$B$4:$B$24))

How it works:

VSTACK combines the date columns from all three sheets into one vertical array. It does the same for the invoiced amount columns. XLOOKUP then searches the combined date array and returns the matching amount. It finds the first match, so this works best when each sheet has unique lookup values, or when you want the first occurrence across all sheets.

When to use VSTACK vs. INDIRECT:

Use INDIRECT when each row should pull from a different sheet based on a cell value (like the division name). Use VSTACK when you want one formula to search across all sheets simultaneously.

Why XLOOKUP Is Better Than VLOOKUP for Cross-Sheet Lookups

There are four practical reasons to prefer XLOOKUP over VLOOKUP for this use case.

No column index. VLOOKUP requires you to count which column number to return (2, 3, etc.). XLOOKUP points directly at the return range. If you insert a column into the source sheet, VLOOKUP breaks. XLOOKUP doesn’t.

Exact match by default. VLOOKUP defaults to approximate match (TRUE) unless you specify FALSE. XLOOKUP defaults to exact match. One less argument to remember, one less source of errors.

Lookup in any direction. VLOOKUP only searches the first column of the table array and returns from a column to the right. XLOOKUP can return from any column, including columns to the left of the lookup column.

Built-in error handling. XLOOKUP has a fourth argument (if_not_found) where you can specify a custom value if no match is found. With VLOOKUP, you need to wrap the formula in IFERROR separately.

When to Stick with VLOOKUP or INDEX MATCH

XLOOKUP requires Microsoft 365 or Excel 2021+. If your files are shared with people on Excel 2019 or earlier, they’ll see a #NAME? error. In that case, use the VLOOKUP + INDIRECT or INDEX MATCH + INDIRECT approach from earlier in this guide.

Also, INDIRECT does not work with closed workbooks in any formula. All referenced workbooks must be open in Excel for INDIRECT to resolve the sheet reference. This applies to VLOOKUP, INDEX MATCH, and XLOOKUP equally.

Download the Workbook

Enhance your learning experience by downloading our workbook. Practice the techniques discussed in real-time and master the INDEX MATCH functions in Excel with hands-on examples.

Download the workbook here and start applying what you’ve learned directly in Excel.

Frequently Asked Questions About Lookups Across Multiple Sheets

Can VLOOKUP pull data from another sheet in Excel?

Yes. Add the sheet name and an exclamation mark before the table array range. For example: =VLOOKUP($B$4, 'Game Div.'!$A$4:$B$24, 2, FALSE). The single quotes around the sheet name are required when the name contains spaces or special characters. Without them, Excel returns a #REF! error.

How do you make a lookup formula switch sheets dynamically?

Use the INDIRECT function to build the sheet reference from a cell value. Instead of hardcoding the sheet name, concatenate the cell reference with the range string: =VLOOKUP($B$4, INDIRECT("'" & B6 & "'!$A$4:$B$24"), 2, FALSE). When B6 contains “Game Div.”, INDIRECT resolves it to 'Game Div.'!$A$4:$B$24. Change the value in B6 and the formula automatically pulls from a different sheet.

The same INDIRECT technique works with XLOOKUP and INDEX MATCH.

What does the INDIRECT function do in Excel?

INDIRECT converts a text string into a cell or range reference that Excel can use in a formula. On its own, =INDIRECT("A1") returns the value in cell A1. Combined with concatenation, it lets you build dynamic references to other sheets: INDIRECT("'" & B6 & "'!$A$4:$B$24") assembles a sheet reference from whatever text is in B6.

One important limitation: INDIRECT only works when the referenced workbook is open. It cannot resolve references to closed files.

Is XLOOKUP better than VLOOKUP for cross-sheet lookups?

For most use cases in Microsoft 365 and Excel 2021+, yes. XLOOKUP doesn’t need a column index number, so inserting columns in the source sheet won’t break it. It defaults to exact match (VLOOKUP defaults to approximate). It can return values from columns to the left of the lookup column. And it has a built-in if_not_found argument for error handling, which VLOOKUP doesn’t.

The only reason to stick with VLOOKUP is backward compatibility. If your files are shared with users on Excel 2019 or earlier, XLOOKUP will return a #NAME? error on their machines.

Can you search all sheets at once with one formula?

Yes, if you’re on Microsoft 365. Use VSTACK inside XLOOKUP to stack the lookup ranges from multiple sheets into a single virtual array:

=XLOOKUP($B$4, VSTACK(Sheet1!$A$4:$A$24, Sheet2!$A$4:$A$24), VSTACK(Sheet1!$B$4:$B$24, Sheet2!$B$4:$B$24))

VSTACK combines the ranges vertically, and XLOOKUP searches the combined array. This returns the first match found across all sheets. It works best when each sheet has unique lookup values or when you only need the first occurrence.

The downside is that sheet names are hardcoded in the formula. If you add a new sheet, you need to update the VSTACK arguments manually. For a fully dynamic setup where the sheet name comes from a cell, use INDIRECT instead.

Why does my cross-sheet VLOOKUP return #REF?

The most common causes are:

The referenced sheet has been renamed or deleted. If the formula points to 'Sales Q1'!A:B and that sheet no longer exists, Excel returns #REF!.

The sheet name has spaces or special characters but is missing the single quotes. =VLOOKUP($B$4, Game Div.!$A$4:$B$24, 2, FALSE) fails because “Game Div.” has a space and a period. Wrap it in quotes: 'Game Div.'!$A$4:$B$24.

You’re using INDIRECT to reference a closed workbook. INDIRECT cannot resolve external references unless the target file is open in Excel. Open the file and the formula will recalculate.

Does INDIRECT work with closed workbooks?

No. This is a hard limitation of the INDIRECT function in all Excel versions. INDIRECT evaluates text strings into references at calculation time, and it can only resolve references to sheets and workbooks that are currently open. If the source workbook is closed, INDIRECT returns a #REF! error.

If you need to pull data from closed workbooks, use Power Query instead. Power Query can connect to external Excel files, refresh on demand, and doesn’t require the source file to be open.

LinkedIn Twitter YouTube

Leila Gharani

Founder of XelPlus and ten-time Microsoft MVP. Leila helps over 500,000 professionals master Excel, Power BI, and data automation through practical, real-world training.

More About Leila Join 500,000+ professionals in our courses