Syntax
XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode])
| Parameter | Description |
|---|---|
| lookup_value | Parameter of the XLOOKUP function. |
| lookup_array | Parameter of the XLOOKUP function. |
| return_array | Parameter of the XLOOKUP function. |
| [if_not_found] | (Optional.) Parameter of the XLOOKUP function. |
| [match_mode] | (Optional.) Parameter of the XLOOKUP function. |
Examples
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | SKU | Price | Stock |
| 2 | Widget | W-100 | 24.99 | 150 |
| 3 | Gadget | G-200 | 49.99 | 75 |
| 4 | Gizmo | Z-300 | 14.99 | 300 |
Simple product price lookup
Formula
=XLOOKUP("Widget", A2:A100, C2:C100)
Returns: 24.99 — finds "Widget" in column A and returns the corresponding price from column C
With default value for not found
Formula
=XLOOKUP(E1, A2:A100, B2:B100, "Not Found")
Returns: the matching value or "Not Found" if the lookup value does not exist in the range
Return multiple columns
Formula
=XLOOKUP("Widget", A2:A100, B2:D100)
Returns: an array with SKU, price, and quantity — all three columns for the matching row
Common Errors
#N/A
The value was not found and no if_not_found argument was provided. Add a fourth argument for a default value.
#VALUE!
The lookup_array and return_array have different sizes. They must have the same number of rows (or columns).
Tips
Built-in error handling
XLOOKUP's 4th argument replaces the need for wrapping with IFERROR: =XLOOKUP(val, range, result, "Not found").
Exact match by default
Unlike VLOOKUP, XLOOKUP defaults to exact match. No more accidental approximate matches from forgetting FALSE.
Search from bottom
Use -1 as the 6th argument to search from the last match: =XLOOKUP(val, range, result, , 0, -1) finds the last occurrence.
Try XLOOKUP in Viztab
Import your data and use XLOOKUP with 370+ other formulas. No signup required.
Open Viztab