XLOOKUP finds a value in one column and returns the matching value from another column. It replaces VLOOKUP and HLOOKUP, and it is easier to use than both. In this guide you will learn the syntax, five practical examples, and how to fix the most common errors.
Quick answer
Formula: =XLOOKUP(what_to_find, where_to_look, what_to_return)
Example: =XLOOKUP("P-102", A2:A20, C2:C20) looks for the product code P-102 in column A and returns its price from column C.
Which Excel versions have XLOOKUP?
XLOOKUP works in Microsoft 365, Excel 2021, Excel 2024 and Excel for the web. Google Sheets also supports it. It is not available in Excel 2019 or older; in those versions use INDEX and MATCH instead (covered at the end).
XLOOKUP syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
| Argument | What it means | Required? |
|---|---|---|
| lookup_value | The value you are looking for | Yes |
| lookup_array | The column (or row) to search in | Yes |
| return_array | The column (or row) with the answer | Yes |
| if_not_found | Text to show when nothing matches | No |
| match_mode | 0 = exact (default), -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard | No |
| search_mode | 1 = first to last (default), -1 = last to first | No |
Sample data
All examples below use this small product list in cells A1:D6.
| A: Code | B: Product | C: Price | D: Stock |
|---|---|---|---|
| P-101 | Wireless Mouse | 8.500 | 42 |
| P-102 | USB-C Hub | 14.000 | 17 |
| P-103 | Laptop Stand | 11.250 | 0 |
| P-104 | Webcam HD | 22.000 | 9 |
| P-105 | Keyboard | 12.750 | 30 |
Example 1: Find a price by product code
- Type the code you want in cell F2, for example
P-104. - In G2, enter
=XLOOKUP(F2, A2:A6, C2:C6). - Press Enter. Excel returns 22.000.
Change F2 to another code and the price updates automatically.
Example 2: Show a friendly message when nothing is found
Without the fourth argument, a missing code shows #N/A. Add your own message instead:
=XLOOKUP(F2, A2:A6, C2:C6, "Code not found")
This replaces the old habit of wrapping VLOOKUP in IFERROR.
Example 3: Look to the left
VLOOKUP can only return values to the right of the search column. XLOOKUP has no such limit. To find the code for a product name:
=XLOOKUP("Webcam HD", B2:B6, A2:A6) returns P-104.
Example 4: Return several columns at once
Select a return range that is more than one column wide, and XLOOKUP “spills” the results into the cells next to it:
=XLOOKUP(F2, A2:A6, B2:D6) returns the product name, price and stock in three cells.
Keep the cells to the right empty, otherwise you will see a #SPILL! error.
Example 5: Find the latest entry
If a list has repeated values (for example, several orders from the same customer), XLOOKUP returns the first match by default. Set search_mode to -1 to search from the bottom up and get the most recent one:
=XLOOKUP("Ahmed", B2:B500, D2:D500, "None", 0, -1)
Bonus: Approximate match for price bands or commissions
Use match_mode -1 to find the closest lower value. With sales thresholds in A2:A5 (0, 1000, 5000, 10000) and commission rates in B2:B5, this returns the right rate for any sales amount in E2:
=XLOOKUP(E2, A2:A5, B2:B5, , -1)
XLOOKUP vs VLOOKUP
| Feature | XLOOKUP | VLOOKUP |
|---|---|---|
| Default match | Exact | Approximate (unless you add FALSE) |
| Look left | Yes | No |
| Breaks when columns are inserted | No | Yes (column number is fixed) |
| Built-in “not found” message | Yes | No |
| Search from bottom | Yes | No |
| Works in Excel 2019 and older | No | Yes |
Common errors and how to fix them
- #N/A: the value was not found. Check for extra spaces (use TRIM), or numbers stored as text.
- #VALUE!: the lookup and return ranges are different sizes. Make sure both start and end on the same rows.
- #SPILL!: a multi-column result is blocked by data in neighbouring cells. Clear those cells.
- #NAME?: your Excel version does not have XLOOKUP. Use INDEX and MATCH instead.
No XLOOKUP? Use INDEX and MATCH
This combination works in every Excel version and gives the same result as Example 1:
=INDEX(C2:C6, MATCH(F2, A2:A6, 0))
FAQ
Is XLOOKUP slower than VLOOKUP?
For normal office files you will not notice a difference.
Can XLOOKUP search with two conditions?
Yes. Join the conditions with &: =XLOOKUP(F2&G2, A2:A100&B2:B100, C2:C100).
Does it work in Google Sheets?
Yes, with the same syntax.