VLOOKUP Across Multiple Sheets in Excel

To do a VLOOKUP across multiple sheets, you chain lookups together: VLOOKUP searches the first sheet, and if the value isn't there, IFERROR hands over to a VLOOKUP on the next sheet. VLOOKUP itself can only search one table at a time. This guide shows the chained formula, a version that handles many sheets with INDIRECT, and the simpler Microsoft 365 method with XLOOKUP and VSTACK.

The example: orders split across sheets#

Many workbooks keep similar data on separate tabs, one per region, month or team. Here, orders are split across three sheets named North, South and East. Each has the same layout:

A Summary sheet lists order numbers in column A, and you want the amount for each one in column B, wherever the order lives. If you only have two sheets, the guide to VLOOKUP between two sheets covers the basics of pointing at another tab.

Why VLOOKUP can't search several sheets at once#

VLOOKUP takes one table as its second argument, and that table must be a single range on one sheet:

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

A 3D reference such as North:East!A2:C100, which works in SUM, doesn't work in VLOOKUP. So you either run several lookups and keep the first one that finds something, or join the sheets into one list first. The VLOOKUP function guide explains each argument.

Method 1: chain VLOOKUPs with IFERROR#

This is the method that works in every version of Excel. When VLOOKUP can't find a value, it returns #N/A. IFERROR catches that error and runs the next formula instead. In B2 on the Summary sheet, type:

=IFERROR(VLOOKUP(A2,North!$A$2:$C$100,3,FALSE),VLOOKUP(A2,South!$A$2:$C$100,3,FALSE))

Excel looks for A2 on North first. If it finds it, you get the amount from column 3. If not, IFERROR runs the second VLOOKUP on South. For three sheets, nest one more IFERROR and finish with a message:

=IFERROR(VLOOKUP(A2,North!$A$2:$C$100,3,FALSE),
 IFERROR(VLOOKUP(A2,South!$A$2:$C$100,3,FALSE),
 IFERROR(VLOOKUP(A2,East!$A$2:$C$100,3,FALSE),"Not found")))

You can type it on one line; the line breaks only make it easier to read (press Alt+Enter on Windows or Option+Return on a Mac to add them in the formula bar). Key points:

• Lock each table with $ so the ranges stay put when you fill down (F4 on Windows, ⌘T on a Mac).
• Use FALSE for an exact match in every VLOOKUP.
• Put the most likely sheet first. Excel stops at the first match, so the order also decides which value wins if an ID appears on two sheets.

To build the sheet reference without typing it, start the formula, click the North tab, select the range, and Excel writes North!A2:C100 for you. The worksheets and cross-sheet references lesson practises exactly this.

IFNA is safer than IFERROR#

IFERROR catches every error, including #REF! from a deleted column or #VALUE! from a broken argument. That can hide a real mistake behind "Not found". IFNA only catches #N/A, the "not found" error, so other problems still show:

=IFNA(VLOOKUP(A2,North!$A$2:$C$100,3,FALSE),IFNA(VLOOKUP(A2,South!$A$2:$C$100,3,FALSE),"Not found"))

IFNA works in Excel 2013 and later. The guide to using IFERROR with VLOOKUP and the IFERROR function guide cover both in more detail.

Method 2: use INDIRECT with a list of sheet names#

Chained formulas get long when you have many sheets. INDIRECT turns text into a reference, so you can keep the sheet names in a list and let the formula pick the right one.

Type the sheet names in H2:H4 (North, South, East) and name the range SheetList (select it and type the name in the Name Box). Then use:

=VLOOKUP(A2,INDIRECT("'"&INDEX(SheetList,MATCH(TRUE,COUNTIF(INDIRECT("'"&SheetList&"'!A2:A100"),A2)>0,0))&"'!A2:C100"),3,FALSE)

Read it from the inside out:

1. INDIRECT("'"&SheetList&"'!A2:A100") builds the ID column of every sheet.
2. COUNTIF(...,A2)>0 returns TRUE for each sheet that contains the order.
3. MATCH(TRUE,...,0) finds the first such sheet, and INDEX returns its name.
4. The outer INDIRECT turns that name into the table for VLOOKUP.

In Microsoft 365 and Excel 2021, press Enter as usual. In older versions, confirm it with Ctrl+Shift+Enter. The apostrophes around the sheet name let it work with names that contain spaces.

INDIRECT has downsides. It is volatile, so it recalculates whenever anything changes, which slows big files. And because the sheet names are text, renaming a tab breaks the formula without warning. Use this method when you have many sheets and the names are stable.

Method 3: XLOOKUP with VSTACK in Microsoft 365#

If you have Microsoft 365 or Excel 2024, VSTACK stacks ranges on top of each other into one list. XLOOKUP can then search that list in one go:

=XLOOKUP(A2,
 VSTACK(North!$A$2:$A$100,South!$A$2:$A$100,East!$A$2:$A$100),
 VSTACK(North!$C$2:$C$100,South!$C$2:$C$100,East!$C$2:$C$100),
 "Not found")

The first VSTACK joins the three ID columns, the second joins the three Amount columns in the same order, so row positions still line up. The last argument is XLOOKUP's built-in "not found" message, so you don't need IFERROR.

In Excel 2021, which has XLOOKUP but not VSTACK, you can still chain lookups neatly by putting the next XLOOKUP in the if_not_found argument:

=XLOOKUP(A2,North!$A$2:$A$100,North!$C$2:$C$100,XLOOKUP(A2,South!$A$2:$A$100,South!$C$2:$C$100,"Not found"))

See the XLOOKUP function guide and XLOOKUP from another sheet for more.

Which method should you use?#

If you keep adding sheets, step back and ask whether the data should be on one sheet with a Region column. One table is easier to look up, filter and pivot. The guide to combining sheets in Excel shows how. If the other data is in a separate file rather than a tab, see VLOOKUP from another workbook.

Common problems#

1. #N/A everywhere. The IDs may be numbers on one sheet and text on another. Check the alignment: text sits on the left of the cell by default.
2. Sheet names with spaces. Write 'North Region'!$A$2:$C$100, with apostrophes around the name.
3. Wrong column number. The column number counts from the first column of the table, not from column A of the sheet.
4. Duplicates on two sheets. Chained lookups return the first match in the order you wrote them. Decide which sheet should win.

Practise it#

Try the exercise on this page: one IFERROR formula falls back from the North table to the South table. Then practise VLOOKUP in a live spreadsheet and handle missing values cleanly in the IFERROR lesson. You type each formula and it checks it instantly.