VLOOKUP Across Sheets in Excel: Formulas and Fixes

Formulas & functions · Updated November 18, 2026

To use VLOOKUP across sheets in Excel, enter a formula such as =VLOOKUP(A2,Products!$A$2:$D$100,3,FALSE) on the sheet where you want the answer. It searches for the value in A2 in the first column of the Products sheet’s range and returns the matching value from that range’s third column.

How VLOOKUP works across sheets

A worksheet reference tells Excel where to find the lookup table. In the example, Products! identifies the worksheet, and $A$2:$D$100 identifies the table range on it. VLOOKUP then uses four arguments: the value to find, the table range, the number of the return column within that range, and whether to use an approximate or exact match.

For a concrete example, suppose an Orders sheet contains a product code in A2, and a Products sheet has product codes in column A, names in B, categories in C, and prices in D. With product code P-104 in Orders!A2 and Notebook in the corresponding row of Products column B, the formula below returns Notebook. The return-column number is 2 because column B is the second column of the selected A:D range—not because B is the second worksheet column in every possible layout.

Assuming Orders!A2 contains P-104 and Products!A2:D100 has product codes, names, categories, and prices in that order, enter this in Orders!B2. It returns Notebook when that code is in the first matching row:

=VLOOKUP(A2,Products!$A$2:$D$100,2,FALSE)

The formula can be entered on any sheet in the same workbook. The lookup value can also be on the lookup-table sheet or in another workbook; what matters is that the value and table references point to the intended cells.

Build the formula step by step

In Excel for Microsoft 365 on Windows, select the cell where the result should appear, type the formula, and press Enter. You can type the sheet reference directly, or begin the formula and click the table sheet and range with the mouse. Excel inserts the worksheet reference as you select the cells.

  1. Choose a result cell on the sheet that needs the lookup result, such as Orders!B2.
  2. Start with =VLOOKUP( and select the cell containing the value you want to find, such as Orders!A2.
  3. Type a comma, select the lookup table on the other sheet, and type another comma.
  4. Enter the return-column number, counting from the first column of the selected range, then enter a comma and FALSE for an exact match.
  5. Close the parenthesis and press Enter. Check that the returned value is from the intended column and row.

For example, if the same table should return the price rather than the product name, use column number 4. Assuming Orders!A2 contains P-104 and Products!A2:D100 uses the column order described above, this returns 4.5 if the matching price is 4.50:

=VLOOKUP(A2,Products!$A$2:$D$100,4,FALSE)

Excel stores numeric values separately from their displayed formatting, so the returned value may display as 4.5 or $4.50 depending on the number format of the result cell. If you want a currency display, format that cell as currency; formatting does not change the lookup itself.

Worksheet names, spaces, and fixed ranges

When a worksheet name has no spaces or punctuation, a reference can look like Products!A2:D100. If its name contains spaces, put the name in single quotation marks. For a sheet named Product List, the reference is 'Product List'!A2:D100. Excel commonly adds the quotation marks automatically when you select the range by clicking the tab.

Dollar signs make the table reference absolute. In the examples, $A$2:$D$100 stays fixed if you copy the formula down a column. Without the dollar signs, Excel adjusts the range as the formula is copied, which can cause later formulas to search a shifted range. The lookup value reference, such as A2, is relative so it changes to A3, A4, and so on as you fill down.

Assuming Orders!A2 contains a code, and a sheet named Product List has the same four-column table in A2:D100, this formula returns the product name and keeps the table fixed when copied down:

=VLOOKUP(A2,'Product List'!$A$2:$D$100,2,FALSE)

For new or growing tables, using a larger range can avoid excluding recently added rows, but an Excel Table is often easier to maintain because it expands as rows are added. VLOOKUP can use a structured table reference, though the table’s first column must still contain the lookup values. If you use a regular cell range, make sure new records are included in the selected range.

Searching more than one worksheet

A single VLOOKUP searches only the range supplied to it; it does not automatically scan every sheet in a workbook. If records are divided between sheets, one approach is to try the first sheet and use IFERROR to try the next if the first lookup does not find a match. The order matters: if the value occurs on both sheets, the first sheet’s result is returned.

Assuming Orders!A2 contains a product code, and North and South each have codes in column A and descriptions in column C, this formula searches North first and then South. If P-104 is absent from North but has the description Notebook in South, it returns Notebook:

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

If the code is missing from both tables, the second VLOOKUP still returns an error. For a workbook used by others, consider returning a clear message instead. Assuming the same layout and that neither sheet contains the code, this version returns Not found:

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

Nested lookups are convenient for a few small, stable lists, but they become cumbersome as the number of sheets grows. If you control the workbook design, combining similar records into one table and adding a column such as Region is usually easier to maintain, filter, and audit than spreading one lookup table over many tabs.

Exact matches and approximate matches

For product codes, employee IDs, invoice numbers, and most other identifiers, use FALSE as the final argument. It asks Excel for an exact match and avoids an approximate result when the lookup table is unsorted. If you leave out the final argument, VLOOKUP uses approximate matching by default, which can produce a plausible but incorrect answer when the data is not sorted as required.

Approximate matching can be useful for thresholds, such as assigning a rate based on a score band. It returns the row for an exact match or, if there is no exact match, the closest smaller lookup value. The first column of the table must be sorted in ascending order for this use. Assuming Commission!A2:B5 contains thresholds 0, 50, 70, and 100 in ascending order and their corresponding rates in column B, a score of 75 returns the rate beside 70. If that rate is 4%, the result is 4%:

=VLOOKUP(75,Commission!$A$2:$B$5,2,TRUE)

Do not use approximate matching merely because the table is large or because it seems faster. It has different meaning from exact matching. A sorted threshold table is appropriate for an interval rule; a product-code table generally calls for an exact match.

Common problems and how to diagnose them

If the result is an error or the wrong value, check the lookup setup in a consistent order. Most issues come from the selected range, the column number, or differences between the lookup value and the stored data.

  • #N/A: With an exact match, Excel could not find the lookup value in the range’s first column. Check spelling, extra spaces, and whether one value is stored as text while the other is numeric. A code such as 0017 may not behave like the number 17, especially if leading zeros matter.
  • Wrong column returned: The column number is counted from the left edge of the selected range. If the range starts at B, then column C is number 2 in that range. Recheck both the range’s first column and the requested return column.
  • #REF!: The column number is greater than the number of columns in the selected range, or a referenced range has been removed. For example, a four-column range cannot return column 5.
  • #VALUE!: The return-column number is invalid, such as zero or a negative number. VLOOKUP needs a column number of at least 1.
  • Unexpected duplicate result: VLOOKUP returns the first matching row it encounters. If the lookup column contains duplicates, it does not return every match or choose the newest one automatically.
  • Formula entry issue: Check that the sheet name is spelled correctly and that names with spaces are enclosed in single quotation marks. In some regional Excel settings, argument separators may be semicolons rather than commas; use the separator expected by that installation.

For text values, invisible spaces can be hard to spot. Compare the source cells carefully and, where appropriate, clean the underlying data before looking it up. Also verify that the table range begins with the key column. VLOOKUP cannot search for a value in the second column of its range and return a value to its left.

VLOOKUP compared with other lookup options

VLOOKUP remains useful when the lookup key is in the leftmost column and the result is to its right. It is widely understood and works in many Excel versions. Its main structural limitation is that the key column must be first in the selected table range; inserting or deleting columns can also make a hard-coded return-column number point somewhere unexpected.

XLOOKUP is available in Microsoft 365 and Excel 2021 or later. It separates the lookup range from the return range, can return values to the left or right, and uses exact matching by default. Assuming Orders!A2 contains P-104, Products!A2:A100 contains codes, and Products!C2:C100 contains categories, this returns the category or the text Not found:

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

INDEX with MATCH is another option, including in older Excel versions. MATCH locates the position of the lookup value, and INDEX returns the value at that position in a separate range. Assuming the same code and category columns as above, and P-104 has Stationery in its matching category row, this returns Stationery:

=INDEX(Products!$C$2:$C$100,MATCH(A2,Products!$A$2:$A$100,0))

Choose based on compatibility and workbook needs. Use VLOOKUP for a straightforward rightward lookup in a workbook where the key is already the first table column. Use XLOOKUP for a more flexible formula when recipients have a compatible Excel version. INDEX/MATCH is useful when compatibility with older Excel releases matters or when separate lookup and return ranges make the formula clearer.

Good practices for reliable sheet lookups

A lookup formula is only as dependable as the data and layout behind it. Before filling a formula across hundreds of rows, test it on a value whose correct answer you can verify. Confirm that the first column of the lookup range contains the intended key and that the return-column number points to the intended field.

  • Use exact matching for identifiers and unique labels.
  • Keep key values consistent: avoid mixing numbers and text representations or adding accidental spaces.
  • Use absolute references for a fixed lookup table when copying formulas.
  • Check for duplicate keys when each key is supposed to identify one record.
  • Use a clear fallback message if a missing match is an expected possibility.
  • Keep related records in one structured table when practical, rather than maintaining many almost-identical sheets.

On Excel for Mac and Excel for the web, the VLOOKUP function and worksheet-reference approach are substantially the same. Ribbon layout and interactions can differ, but the formula syntax and the requirement that the key be in the first column of the lookup range do not change. The formulas here use English function names and comma separators; regional settings may display different argument separators.

Frequently Asked Questions

Can VLOOKUP pull data from another sheet in the same workbook?
Yes. Put the sheet name and an exclamation mark before the lookup range, such as Products!$A$2:$D$100. If the sheet name has spaces, wrap it in single quotation marks, as in 'Product List'!$A$2:$D$100.

Does VLOOKUP search every sheet automatically?
No. Each VLOOKUP searches only the range specified in its formula. You can chain lookups with IFERROR for a small number of sheets, but for a larger collection it is usually clearer to combine the records into one table.

Why does VLOOKUP return #N/A when the value appears to be there?
The stored values may differ even if they look alike. Check for leading or trailing spaces, text-versus-number differences, punctuation, and leading zeros. Also make sure the lookup table range starts with the column containing the lookup value.

Can VLOOKUP return a value from a column to the left?
No. The lookup key must be in the first column of the selected range, and VLOOKUP can return only from that column or a column to its right. XLOOKUP or INDEX/MATCH can handle a return range to the left.

What does the last argument FALSE mean?
It tells VLOOKUP to look for an exact match. That is generally the right choice for codes, names, and IDs. TRUE requests approximate matching, which is intended for ordered thresholds and requires the first lookup column to be sorted ascending.

Will VLOOKUP return every matching row?
No. It returns the first matching row in the lookup range. If you need all records that meet a condition, use a filtering approach such as Excel’s filter tools or, in Microsoft 365 and Excel 2021, the FILTER function rather than expecting VLOOKUP to spill multiple matches.

Related

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.