VLOOKUP Examples for Multiple Sheets in Excel

To look up a value on another worksheet, use VLOOKUP with that sheet’s range, such as =VLOOKUP(A2,Products!$A$2:$C$100,2,FALSE). To search several worksheets in order, nest one exact-match VLOOKUP inside another with IFERROR; Excel checks each sheet until it finds a match.

How VLOOKUP works across worksheets

VLOOKUP does not need the lookup table to be on the same worksheet as the formula. Its basic structure is =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]). The worksheet name becomes part of the table_array reference, while the other arguments work as they do in a same-sheet lookup.

For example, imagine an Orders sheet with product codes in column A and a Products sheet with product codes in column A, names in B, and prices in C. The formula below returns the product name associated with the code in Orders!A2.

Assumed layout: on Orders, the code to find is in A2; on Products, codes are in A2:A100 and names are in B2:B100. Enter this in Orders!B2: =VLOOKUP(A2,Products!$A$2:$C$100,2,FALSE). If A2 contains P-104 and that row’s name is Desk Lamp, the result is Desk Lamp.

The range begins in Products column A, so 2 tells VLOOKUP to return the second column within the selected range, which is column B. The final argument FALSE requests an exact match. For product codes, employee IDs, invoice numbers, and most other identifiers, exact matching is usually the right choice.

Search more than one sheet in order

A VLOOKUP call searches one table_array. To check multiple worksheets, use IFERROR to try the next lookup when the current one returns an error. The following example checks North first, then South, then West. Each sheet must have the same kind of table: codes in column A and the value to return in column C.

Assumed layout: the lookup code is in A2 on the current sheet, and each regional sheet has codes in A2:A100 and quantities in C2:C100. Enter this formula in the result cell: =IFERROR(VLOOKUP(A2,North!$A$2:$C$100,3,FALSE),IFERROR(VLOOKUP(A2,South!$A$2:$C$100,3,FALSE),IFERROR(VLOOKUP(A2,West!$A$2:$C$100,3,FALSE),"Not found"))). If A2 is found on South and the matching value in South column C is 24, the formula returns 24. If no sheet contains the code, it returns Not found.

Order matters: Excel returns the first match it reaches. If the same code appears on North and South, this formula returns North’s value, even if South’s row is newer. Decide which sheet should take priority, and consider whether duplicate codes need to be resolved before relying on the result.

To add another worksheet, insert another IFERROR-wrapped lookup before the final Not found result. Keep the parentheses balanced, and use the same exact-match setting and compatible column layout in every lookup. A long chain is workable for a few sheets, but it becomes harder to read and maintain as the number of sheets grows.

Sheet names, spaces, and fixed ranges

Excel accepts an unquoted sheet name when it contains no spaces or certain special characters, as in North!$A$2:$C$100. If a sheet name contains spaces, put it in single quotes: =VLOOKUP(A2,'Price List'!$A$2:$C$100,3,FALSE). This example assumes the key is in A2, and the Price List sheet has the lookup column in A and the return value in C. If the matching row contains 19.95 in column C, the result is 19.95.

When a sheet name contains an apostrophe, double the apostrophe inside the quoted name. For a sheet called O’Brien Data, the worksheet reference uses ‘O”Brien Data’! before the range. You can also build a sheet reference by typing an equals sign in a cell, clicking the target worksheet and selecting the range; Excel inserts the reference syntax for you.

The dollar signs in $A$2:$C$100 make the lookup range absolute. They keep it from shifting when you copy the formula down. The lookup value A2 is relative, so it changes to A3, A4, and so on as you fill the formula into later rows. In Excel for Microsoft 365 on Windows, select the formula cell and press Ctrl+D to fill down after selecting the destination cells. On Mac, use Command+D. Excel for the web also supports these formulas; shortcuts can vary with the browser and operating system.

Build a practical lookup example

Suppose a staff list is divided among East, Central, and West sheets. Each sheet has an employee ID in column A, a name in B, and a department in C. On a Summary sheet, you want to return the department for each ID. This formula checks the three sheets and uses a clear fallback when an ID is absent.

Assumed layout: Summary!A2 contains an employee ID; East, Central, and West each hold IDs in A2:A200 and departments in C2:C200. Enter this in Summary!B2: =IFERROR(VLOOKUP(A2,East!$A$2:$C$200,3,FALSE),IFERROR(VLOOKUP(A2,Central!$A$2:$C$200,3,FALSE),IFERROR(VLOOKUP(A2,West!$A$2:$C$200,3,FALSE),"Check ID"))). If the ID is found only on Central and the department there is Support, the result is Support; if it is absent from all three sheets, the result is Check ID.

Before copying the formula down, confirm that all three source tables follow the same column arrangement. VLOOKUP always searches the first column of the selected range, and the column number counts from the left edge of that range, not from worksheet column A. If a table range starts at B, then its second column is worksheet column C. Also check that IDs are stored consistently: a number stored as text may not match the same-looking numeric value.

Exact matches and VLOOKUP’s limits

Use FALSE for the fourth argument when you need an exact match. If you omit this argument, VLOOKUP uses approximate-match behavior, which can return an unexpected result when the first column is not sorted in ascending order. Approximate matching is useful for deliberately arranged threshold tables, such as grading bands, but it is not a safe default for searching IDs or names.

VLOOKUP also requires the lookup column to be the leftmost column in the selected range. It can return information to the right of that column, but not to its left. Its column number is a fixed number, so inserting or rearranging columns can make a formula return a different field than intended. These constraints apply whether the table is on the current sheet or another one.

For a reliable workbook, use consistent headings and layouts on source sheets, avoid duplicate lookup keys unless you have a clear priority rule, and make the fallback text specific enough to prompt a useful action. “Check ID” is more informative than a blank result when a missing record may indicate a data-entry problem.

Common errors and ways to troubleshoot

#N/A: The exact key may not exist, or the key and source values may differ because of extra spaces, different data types, or a spelling variation. Check the source sheet and confirm the lookup value is in the first column of the selected range.
#REF!: The column index is greater than the number of columns in table_array, or a referenced sheet or range has been removed. For a range A:C, valid return-column numbers are 1, 2, and 3.
Wrong result: Check for duplicate keys and verify the sheet-checking order. Also confirm that the column index points to the intended return field and that FALSE is the last VLOOKUP argument.
Formula shows as text: The cell may be formatted as Text, or the formula may begin with an apostrophe. Change the cell format to General, then re-enter the formula.
Broken sheet reference: Check spelling and punctuation in the sheet name. Names with spaces need single quotes around the name; Excel may adjust references when you rename a worksheet, but references to deleted sheets can break.

IFERROR catches any error raised by its first argument, not just a missing match. That is convenient for a short search chain, but it can conceal a damaged reference or an incorrect column index by treating it like “not found” and moving on. If troubleshooting matters, test each VLOOKUP on its own first, or use a more explicit design that distinguishes missing records from formula problems.

Alternatives for larger workbooks

If many sheets contain portions of one dataset, combining them into a single well-structured table is often easier than maintaining a long chain. You can then use one lookup formula, filter or summarize the combined data, and avoid accidental differences between regional layouts. Power Query is one option in Excel for Microsoft 365: use Data > Get Data to connect to workbook data, then use the Power Query editor to append tables with matching columns. The exact available import choices depend on where the source data is stored.

For a few sheets, XLOOKUP can make the return and lookup ranges more explicit and does not require a numeric column index. XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It searches an individual range, so a multi-sheet search still needs a fallback chain or a combined data source.

Assumed layout: A2 contains a code, and North and South each have codes in A2:A100 and prices in C2:C100. Enter this formula to check North and then South: =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")). If the code is absent from North but its price on South is 12.5, the formula returns 12.5. XLOOKUP uses exact matching by default, though its optional match_mode can change that behavior.

Another useful alternative is a single master table with a source or region column. This makes it easier to see where records came from and to handle repeated keys intentionally. Choose a chained lookup for a small, stable set of sheets; choose consolidation when sheets proliferate, data needs regular reporting, or users need to audit duplicates.

Frequently Asked Questions

Can VLOOKUP search multiple sheets automatically?
Not with one ordinary table_array argument. Use a chain of IFERROR-wrapped lookups to check sheets one by one, or combine the source tables into one table and search that.

Does VLOOKUP work with sheet names that contain spaces?
Yes. Enclose the sheet name in single quotes before the exclamation mark, as in ‘Price List’!$A$2:$C$100. Excel may add the quotes automatically when you select the range.

Which sheet does a multiple-sheet formula search first?
It searches in the order the lookups appear in the formula. With nested IFERROR calls, the first sheet is checked first, and a later sheet is checked only if the earlier lookup returns an error.

Can VLOOKUP return a value from a column to the left?
No. The lookup key must be in the leftmost column of the selected table_array, and VLOOKUP returns a column to its right. Reorganize the table or use a different lookup approach if the return field is on the left.

Why does my formula return #N/A even though the value looks the same?
The values may differ in a way that is not obvious, such as a number stored as text, an extra space, or a different character. Compare the source and lookup values, check their data types, and clean inconsistent data where appropriate.

Is XLOOKUP better than VLOOKUP for multiple sheets?
XLOOKUP removes the column-number argument and can return from either side of its lookup range, but it still searches one range per call. To search multiple sheets, you must still chain lookups or consolidate the data.

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.