VLOOKUP
=IFNA(VLOOKUP(A2,Products!A:C,3,FALSE),"Not found")Works when the lookup column is first.
PASTE-READY RECIPE
Lookup a Value from Another Sheet in Google Sheets
Match an ID on one tab and return a field from another tab.
QUICK ANSWER
The formula and where it fits.
Match an ID on one tab and return a field from another tab. Use =XLOOKUP(A2,Products!A:A,Products!C:C,"Not found"), then replace the sample ranges with the columns in your sheet.
XLOOKUP matches A2 against the product ID column and returns the corresponding price.
- Put the source data under one header row.
- Paste the formula into an empty result cell.
- Change the ranges and criteria to match your sheet.
- Check the result against one row you can verify manually.
TRY ITEdit the table.
Edit the table.
Watch the result change.
| A | |
|---|---|
| 1 | Product ID |
| 2 | |
| 3 | |
| 4 |
FORMULA OUTPUT$24 · $41 · Not foundRecalculates from the input cells above
VARIATIONS
Use the version that fits your sheet.
INDEX MATCH
=IFNA(INDEX(Products!C:C,MATCH(A2,Products!A:A,0)),"Not found")Works in older Sheets files.
Import another file
=XLOOKUP(A2,IMPORTRANGE(F1,"Products!A:A"),IMPORTRANGE(F1,"Products!C:C"))Reads data from a separate spreadsheet.
COMMON ERRORS
When the result looks wrong.
The sheet name contains spaces
Wrap the sheet name in single quotes.
Numbers and text do not match
Convert both ID columns to the same data type.
QUESTIONS
Answers before you paste.
The sheet name contains spaces
Wrap the sheet name in single quotes.
Numbers and text do not match
Convert both ID columns to the same data type.
MAKE IT YOURS
Adjust this formula →Change the ranges, labels or criteria.
Send this recipe to the SheetSteps assistant and describe how your columns are arranged.