How to Use VLOOKUP Across Multiple Sheets in Excel

/ How To Fix, Office & Email / By

VLOOKUP works across sheets the same way it works on one sheet. The main difference is the table range: it needs the sheet name, an exclamation point, and the cell range.

Does this affect you?

Use this for Excel for Microsoft 365, Excel 2021, Excel 2019, and Excel Online when lookup data is on another tab or spread across several tabs.

VLOOKUP from another sheet

Let Excel insert the sheet reference so the syntax is right.

  1. Click the result cell.
  2. Type =VLOOKUP( and select the lookup value.
  3. Type a comma.
  4. Click the other sheet tab and select the lookup table range.
  5. Add the return column number, counting from the first column in that selected range.
  6. Add FALSE for an exact match, close the formula, and press Enter.

A complete formula might look like =VLOOKUP(A2,Prices!A2:D100,3,FALSE). It searches A2 on the Prices sheet and returns the third column from that range.

Search several sheets in order

Use IFERROR to try the next sheet when a lookup is not found.

  1. Decide the order the sheets should be checked.
  2. Write the first VLOOKUP.
  3. Wrap it in IFERROR and place the next VLOOKUP as the fallback.
  4. Repeat for each sheet.
  5. Use a pattern such as =IFERROR(VLOOKUP(A2,Jan!A:D,3,FALSE),IFERROR(VLOOKUP(A2,Feb!A:D,3,FALSE),VLOOKUP(A2,Mar!A:D,3,FALSE))).

Excel uses the first sheet that returns a match. If the formula grows beyond a few sheets, consider combining the data into one table.

More control

Lookups in separate workbooks

When the source is in another file, open both workbooks while building the formula. Excel includes the file name in brackets. Closed workbook links may not update until the source file is available again.

Use exact matches

For IDs, names, and most lookup lists, use FALSE as the final VLOOKUP argument so Excel does not return an approximate result.

Consolidate repeated monthly tabs

If you regularly search many month or region sheets, append the data into one master table with Power Query or another consolidation method, then run one lookup.

Sources

  • Microsoft Support – VLOOKUP function (2025)
  • Microsoft Support – Create a reference to the same cell range on multiple worksheets (2025)
  • Microsoft Support – IFERROR function (2025)
Disclosure: This post may contain affiliate links which means I may receive a commission for purchases made through links. I will only recommend products that I have personally used! Learn more on my Private Policy page.
A thoughtful woman reads a newspaper while enjoying coffee at an indoor workspace.

DEALWEEK

SUBSCRIBE AND GET 20% OFF YOUR NEXT ORDER! OFFER ENDS SOON - DON’T MISS OUT!

We don’t spam! Read our privacy policy for more info.

Shopping Cart