SheetSense

VLOOKUP from another sheet or file

Put the sheet name and an exclamation mark in front of the lookup range and VLOOKUP reads its table from a different tab.

fx
=VLOOKUP(A2,'Price List'!$A:$B,2,FALSE)

The sheet: The Orders tab has a SKU in column A. A tab named Price List has SKU in column A and Price in column B.

How it works, step by step

  1. 01A2 is the SKU to find on the current row.
  2. 02'Price List'!$A:$B is the table on the other tab. Quotes are needed because the name has a space.
  3. 03The 2 returns the second column of that range, Price.
  4. 04FALSE asks for an exact match.

The same logic in four tools

Excel
=VLOOKUP(A2,'Price List'!$A:$B,2,FALSE)
Google Sheets
=VLOOKUP(A2,'Price List'!$A:$B,2,FALSE)
For a different Google Sheets file: =VLOOKUP(A2,IMPORTRANGE("https://docs.google.com/spreadsheets/d/FILE_ID","'Price List'!A:B"),2,FALSE)
SQL
SELECT o.sku, p.price
FROM orders o
LEFT JOIN price_list p ON p.sku = o.sku;
Python (pandas)
merged = orders.merge(price_list[["sku", "price"]], on="sku", how="left")

Mistakes that break it

  • !If the tab name contains a space or symbol it must be wrapped in single quotes.
  • !For another Google Sheets file you must use IMPORTRANGE and allow access the first time.
  • !SKUs stored as text in one tab and numbers in the other will not match.

Common questions

Why do I get #N/A?
The value is not in the first column of the range, or it differs by a trailing space or by text versus number type.
Can I look up from another Excel workbook?
Yes, by selecting the range in the other open workbook, which adds a [Book.xlsx] reference. The link breaks if that file moves.

Got a version of this with your own ranges?

Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.