How to VLOOKUP from Another Sheet in Google Sheets
Use =VLOOKUP(A2,Sheet2!A:B,2,FALSE) to look up the value in A2 against a table on Sheet2 and return the matching value from its second column.
=VLOOKUP(A2,Sheet2!A:B,2,FALSE)
The Sheet2! prefix points the range at another tab. FALSE forces an exact match. Put quotes around a tab name with spaces: 'My Data'!A:B.
How it works
To look up data on a different tab, prefix the range with the sheet name and an exclamation mark: Sheet2!A:B. Everything else works like a normal VLOOKUP — the search key, the column index to return, and FALSE for an exact match. If the data lives in a completely separate spreadsheet file, wrap IMPORTRANGE as the table argument. Tab names containing spaces must be wrapped in single quotes.
Variations
Tab name with spaces
=VLOOKUP(A2,'Sales Data'!A:B,2,FALSE)
Single quotes are required around multi-word tab names.
From a different spreadsheet file
=VLOOKUP(A2,IMPORTRANGE("URL","Sheet1!A:B"),2,FALSE)
Replace URL with the other file's link; approve access once when prompted.
Show blank instead of #N/A
=IFERROR(VLOOKUP(A2,Sheet2!A:B,2,FALSE),"")
Returns empty when no match is found.
Examples
| Scenario | Formula |
|---|---|
| Price from a products tab | =VLOOKUP(A2,Products!A:C,3,FALSE) |
| From another file | =VLOOKUP(A2,IMPORTRANGE("URL","Data!A:B"),2,FALSE) |
FAQ
How do I VLOOKUP from another tab?
Prefix the table range with the tab name: =VLOOKUP(A2,Sheet2!A:B,2,FALSE).
What if the tab name has spaces?
Wrap it in single quotes: 'Sales Data'!A:B.
How do I VLOOKUP from a different spreadsheet file?
Use IMPORTRANGE as the table: =VLOOKUP(A2,IMPORTRANGE("URL","Sheet1!A:B"),2,FALSE), then approve access once.