Lookup formula
XLOOKUP in Excel and Google Sheets
Find a value in one column and return the matching value from another column.
Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])- lookup_value
- What you are looking for, e.g. a product name.
- lookup_array
- The column to search in.
- return_array
- The column to return a value from.
- if_not_found
- Optional text to show when nothing matches.
Example
| Row | A | B |
|---|---|---|
| 1 | Product | Price |
| 2 | Apple | 12 |
| 3 | Pear | 15 |
| 4 | Plum | 9 |
| 5 | ||
| 6 | Find | Pear |
B7 =XLOOKUP(B6,A2:A4,B2:B4,"Not found")
Result: 15
Searches A2:A4 for "Pear" and returns the price next to it.
Ready-to-copy formulas
Look up a price by product name
=XLOOKUP(A2,Products!A:A,Products!B:B,"Not found")Return the last match
=XLOOKUP(A2,B:B,C:C,,0,-1)Return several columns at once
=XLOOKUP(A2,Products!A:A,Products!B:D)
Tips
- Works in Excel 365, Excel 2021 and Google Sheets.
- Unlike VLOOKUP, the return column can be to the left of the lookup column.
- Use the if_not_found argument instead of wrapping in IFERROR.
Need the whole spreadsheet?
Start from a free template with every formula already in place. Opening a template uses no credits.