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.
=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
- 01A2 is the SKU to find on the current row.
- 02'Price List'!$A:$B is the table on the other tab. Quotes are needed because the name has a space.
- 03The 2 returns the second column of that range, Price.
- 04FALSE asks for an exact match.
The same logic in four tools
=VLOOKUP(A2,'Price List'!$A:$B,2,FALSE)=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)SELECT o.sku, p.price
FROM orders o
LEFT JOIN price_list p ON p.sku = o.sku;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.