Pull a matching value from a table on a different worksheet with VLOOKUP.
Copy this formula
=VLOOKUP(A2, Sheet2!$A:$C, 3, FALSE)Reference the other sheet by name before the range (Sheet2!). VLOOKUP finds A2 in the first column of that range and returns the value from the 3rd column. Always use FALSE for an exact match.
=VLOOKUP(A2, Prices!$A:$B, 2, FALSE)→$19.90Returns the price for the product in A2 from the Prices sheet.
=IFERROR(VLOOKUP(A2, Prices!$A:$B, 2, FALSE), "Not found")→Not foundShows a message instead of #N/A when there is no match.
=INDEX(Prices!$B:$B, MATCH(A2, Prices!$A:$A, 0))→$19.90INDEX and MATCH give the same result and keep working when columns are inserted.
Modern (Excel 365)
=XLOOKUP(A2, Sheet2!A:A, Sheet2!C:C)XLOOKUP can look left and returns a clean result without column counting.
The lookup value isn't found. Check for trailing spaces (wrap in TRIM), mismatched data types, or that you used FALSE for exact match.
Wrap the name in single quotes: =VLOOKUP(A2, 'Price List'!$A:$B, 2, FALSE).
Yes. Put the file name in square brackets: =VLOOKUP(A2, [Prices.xlsx]Sheet1!$A:$B, 2, FALSE). Keep the other file in the same location so the link stays valid.
The fourth argument is TRUE or missing, so Excel looks for an approximate match. Set it to FALSE for an exact match.
Need a formula for your own data?
Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.