Google Sheets functions / Lookup & Reference
Lookup & Reference function · Google Sheets
XLOOKUP function
XLOOKUP finds a value in one range and returns the matching value from another. It looks left or right, defaults to an exact match, and has a built-in message for missing items.
Syntax
=XLOOKUP(search_key, lookup_range, result_range, [missing_value], [match_mode], [search_mode])Arguments
| Argument | Required | Meaning |
|---|---|---|
| lookup_value | Yes | The value to find. |
| lookup_array | Yes | The range to search. |
| return_array | Yes | The range that holds the answer. |
| if_not_found | No | Text or a value to show when nothing matches. |
| match_mode | No | 0 exact (default), -1 next smaller, 1 next larger, 2 wildcard. |
| search_mode | No | 1 first to last (default), -1 last to first. |
Worked example
| A | B | C | |
|---|---|---|---|
| 1 | SKU | Product | Price |
| 2 | A-101 | Keyboard | 45 |
| 3 | A-102 | Mouse | 22 |
| 4 | A-103 | Monitor | 180 |
| 5 | A-104 | Dock | 95 |
=XLOOKUP("A-103",A2:A5,C2:C5,"Not found")Result: 180
The formula searches the SKU column for A-103 and returns the price from the same row.
Try it
Edit the formula and run it against the table above. Results come from a browser spreadsheet engine.
Availability
| Platform | Status |
|---|---|
| Excel | Microsoft 365, Excel 2024, Excel 2021, Excel for the web. Not in Excel 2019 or older. |
| Google Sheets | Available. |
Common mistakes
- Lookup and return ranges with different sizes return an error.
- A number stored as text will not match a real number.
- Workbooks shared with Excel 2019 users will show an error for this function.
Do it with AI
Paste this prompt into Copilot, Gemini or another assistant. Check the result on a small sample first.
I have SKUs in column A and prices in column C. Write an XLOOKUP that returns the price for the SKU in F2 and shows "Not found" when it is missing.