Quick Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [not_found], [match_mode], [search_mode])
Only the first three arguments are required. not_found defaults to #N/A; match_mode defaults to exact match (0); search_mode defaults to first-to-last (1).
What Is XLOOKUP?
XLOOKUP is a lookup function introduced in Excel 365 and Excel 2021 that searches a range for a value and returns a corresponding result from another range. It was designed to replace both VLOOKUP and HLOOKUP and fixes their most frustrating limitations.
Key advantages over VLOOKUP:
- Looks in any direction — left, right, up, or down
- No column index number needed — you specify the return range directly
- Defaults to exact match (VLOOKUP defaults to approximate, a common source of errors)
- Built-in "not found" handling — no need to wrap with IFERROR
- Can return a whole row or column of results at once
- Works with wildcards and approximate numeric matches
For the official function reference, see Microsoft's XLOOKUP documentation.
Basic XLOOKUP Example
Find a product price by name:
' Products in A2:A100, Prices in B2:B100
=XLOOKUP("Widget", A2:A100, B2:B100)
' With a custom "not found" message:
=XLOOKUP("Widget", A2:A100, B2:B100, "Product not found")Compare this to VLOOKUP for the same task:
=IFERROR(VLOOKUP("Widget", A2:B100, 2, FALSE), "Product not found")XLOOKUP is shorter, clearer, and the return range (B2:B100) stays correct even if you insert a column between A and B — with VLOOKUP, inserting a column would silently return the wrong data.
XLOOKUP Syntax Explained
Argument 1: lookup_value
The value you are searching for. Can be a cell reference, a hardcoded value, or a formula result. XLOOKUP supports wildcards here: use * for any text and ? for a single character (requires setting match_mode to 2).
Argument 2: lookup_array
The range to search in. Must be a single row or column — not a multi-column range. Unlike VLOOKUP, this range does not need to be on the left side of your data.
Argument 3: return_array
The range to return results from. Can be a single column, a single row, or even a multi-column range (in which case XLOOKUP returns the entire matching row). Must be the same size as lookup_array.
Argument 4: if_not_found (optional)
What to return when no match is found. Use "" for blank, "Not found" for text, or 0 for zero. If omitted, XLOOKUP returns #N/A — same as VLOOKUP.
Argument 5: match_mode (optional)
0— Exact match (default). Returns #N/A or your not_found value if no exact match exists.-1— Exact match or next smaller. Useful for tiered pricing or grade bands.1— Exact match or next larger.2— Wildcard match. Use with*and?in the lookup value.
Argument 6: search_mode (optional)
1— Search first to last (default).-1— Search last to first. Use this to find the most recent match when duplicates exist.2— Binary search (ascending sorted data). Much faster on very large ranges.-2— Binary search (descending sorted data).
Common XLOOKUP Patterns
Look Left (Impossible with VLOOKUP)
' Return the product code (column A) for a given price (column C)
=XLOOKUP(D2, C2:C100, A2:A100, "Not found")Two-Way Lookup (Row and Column)
' Find the value at the intersection of a matching row and column
' Months in row 1, Products in column A, Data in B2:M50
=XLOOKUP(A52, A2:A50, XLOOKUP(B52, B1:M1, B2:M50))Find the Last Match
' Return the most recent price for a product that appears multiple times
=XLOOKUP("Widget", A2:A100, B2:B100, "Not found", 0, -1)Return Multiple Columns at Once
' Return both price AND stock quantity in one formula (spills into 2 cells)
=XLOOKUP("Widget", A2:A100, B2:C100)Wildcard Match
' Find any product name containing "Widget"
=XLOOKUP("*Widget*", A2:A100, B2:B100, "Not found", 2)Nested XLOOKUP for Approximate (Tiered) Lookup
' Commission rate based on sales tier (tiers in D2:D5, rates in E2:E5)
' Returns the rate for the largest tier <= the sales amount in A2
=XLOOKUP(A2, D2:D5, E2:E5, , -1)XLOOKUP vs VLOOKUP vs INDEX/MATCH
| Feature | XLOOKUP | VLOOKUP | INDEX/MATCH |
|---|---|---|---|
| Look left | ✓ Yes | ✗ No | ✓ Yes |
| Default match type | Exact | Approximate | Exact |
| Built-in not-found | ✓ Yes | ✗ No (need IFERROR) | ✗ No (need IFERROR) |
| Return multiple columns | ✓ Yes | ✗ No | ✓ With MATCH |
| Breaks on column insert | ✗ No | ✓ Yes | ✗ No |
| Excel version support | 365/2021+ | All versions | All versions |
If you're on Excel 365 or 2021, use XLOOKUP by default. If you need compatibility with Excel 2019 or earlier, use INDEX/MATCH — it provides most of the same benefits.
Why Is XLOOKUP Returning #N/A?
Even with its improved design, XLOOKUP can still return #N/A when no match is found. Common causes:
- Data type mismatch: The lookup value is a number but the lookup array contains text-formatted numbers. Use
=VALUE()or convert the column to numbers. - Extra spaces: Wrap the lookup value in
TRIM():=XLOOKUP(TRIM(A2), ...) - Case sensitivity: XLOOKUP is not case-sensitive. "WIDGET" and "widget" match. If you need case-sensitive lookup, use INDEX/MATCH with EXACT.
- The value genuinely doesn't exist: Use the not_found argument to return something meaningful instead:
=XLOOKUP(A2, B:B, C:C, "Not in list")
If XLOOKUP errors are spreading across your workbook, the ExcelErrorFinder audit tool will identify every affected cell across all sheets.
XLOOKUP Is Not Available — What to Do
XLOOKUP requires Excel 365 or Excel 2021. If you or your recipients use an older version, use INDEX/MATCH instead:
' XLOOKUP equivalent using INDEX/MATCH:
=IFERROR(INDEX(return_range, MATCH(lookup_value, lookup_range, 0)), "Not found")