fx
FunctionPedia
Quick Answer

XLOOKUP Function

XLOOKUP is the upgraded VLOOKUP — it can search left or right, doesn't require a column index number, handles missing values with a default result, and supports nested arrays. Available in Excel 365 and Excel 2021+.

✓ Excel✓ Google SheetsExcel 365 / 2021+

Syntax

XLOOKUP
(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])

Parameters

ParameterDescriptionRequired
lookup_valueThe value you want to find.Required
lookup_arrayThe range or array to search through.Required
return_arrayThe range or array to return values from.Required
if_not_foundWhat to return if no match is found (instead of #N/A).Optional
match_mode0 = exact match, -1 = exact or next smaller, 1 = exact or next larger, 2 = wildcard.Optional
search_mode1 = first-to-last, -1 = last-to-first, 2 = binary search (ascending), -2 = binary search (descending).Optional

Basic Example

Find a product price by looking up the product ID

=XLOOKUP("A102", B2:B50, E2:E50)
Result$29.99

This searches for "A102" in B2:B50 and returns the corresponding value from E2:E50. No column number needed — just the return range.

Advanced Examples

Example 1: XLOOKUP with default value for missing items

Data validation

Return 'Not found' instead of #N/A when the lookup value doesn't exist

=XLOOKUP(E2, A2:A100, D2:D100, "Not found")
Result: Not found (if missing)
The 4th argument 'if_not_found' replaces #N/A with your custom message. No need for IFERROR wrapper.

Example 2: XLOOKUP searching left (reverse lookup)

Flexible lookup

Find an employee's ID by looking up their name — searching left, which VLOOKUP cannot do

=XLOOKUP("John Smith", C2:C100, A2:A100)
Result: EMP-0042
XLOOKUP can return values from a column that's to the LEFT of the search column — something VLOOKUP cannot do.

Example 3: XLOOKUP returning multiple columns

Data extraction

Return an entire row of data (multiple columns) with a single XLOOKUP

=XLOOKUP(E2, A2:A100, B2:D100)
Result: Returns 3 values: name, department, salary
When return_array spans multiple columns, XLOOKUP spills results across adjacent cells — returning entire rows in one formula.

How XLOOKUP Works

XLOOKUP fundamentally improves on VLOOKUP in three ways. First, it separates the lookup array and return array — you specify exactly which column to search and which to return, so it can look left or right. Second, it has a built-in 'if_not_found' argument that handles missing values without wrapping in IFERROR. Third, it supports advanced match modes including wildcard matching and binary search for large datasets. Unlike VLOOKUP, XLOOKUP doesn't break when you insert or delete columns because it uses range references instead of a column index number.

1
Identify your lookup value
Decide what you're searching for — this can be a cell reference, text, or number.
2
Select the lookup array
Specify the single column or row where the search happens.
3
Select the return array
Choose the column or row containing the data you want to retrieve. It can be anywhere — left, right, or even on a different sheet.
4
Optional: set if_not_found
Add a custom message or value for when no match is found, like 'Not found' or 0.
5
Enter the formula
Type =XLOOKUP(lookup, search_range, return_range, default) in your result cell.

Important Notes & Limitations

  • XLOOKUP is only available in Excel 365 and Excel 2021+ — not in Excel 2019 or earlier versions.

  • Google Sheets does not have XLOOKUP (use FILTER, INDEX/MATCH, or VLOOKUP as alternatives).

  • For very large datasets (100K+ rows), binary search mode (search_mode=2/-2) requires sorted data.

  • When return_array spans multiple columns, results spill into adjacent cells — those cells must be empty.

Common Errors & Fixes

#VALUE!lookup_array and return_array have different sizes (different number of rows or columns)

Fix: Ensure both arrays have the same length. For example, if lookup_array is A2:A100, return_array should also span 99 rows.

#N/A (if no if_not_found set)The lookup value doesn't exist in the lookup_array

Fix: Add a 4th argument to provide a default value: =XLOOKUP(val, range, result_range, "Not found")

#SPILL!Return array spans multiple columns and adjacent cells aren't empty

Fix: Clear the cells where XLOOKUP needs to spill results, or use a single-column return array.

Download Practice File

Practice XLOOKUP with Real Data

Download a sample CSV file with pre-populated data and practice exercises for the XLOOKUP function. Works in both Excel and Google Sheets.

Works in Google SheetsCompatible with ExcelIncludes exercises

File format: CSV (comma-separated values) - opens in Excel, Google Sheets, and all spreadsheet apps

Related Tutorials

INDEX MATCH Formula

Master the INDEX MATCH formula in Excel and Google Sheets step by step: exact syntax, left lookups, two-way lookups, and how it compares to VLOOKUP and XLOOKUP.

Nested If Alternatives

Long nested IF formulas are hard to read and easy to break. Discover the best nested IF alternatives in Excel and Google Sheets — IFS, lookup tables, and XLOOKUP.

VLOOKUP with Multiple Criteria

VLOOKUP only matches one column, but real lookups often need two or more. Learn four reliable ways to do a VLOOKUP with multiple criteria in Excel and Google Sheets.

Frequently Asked Questions

Is XLOOKUP better than VLOOKUP?
Yes, in every way. XLOOKUP can look left, doesn't use column numbers (so it won't break when columns change), handles missing values natively, and can return multiple columns at once. The only downside is availability — it's only in Excel 365/2021+.
Can XLOOKUP search from bottom to top?
Yes. Set search_mode to -1 to search last-to-first. This is useful when you want the most recent entry in a chronological list.
How do I use XLOOKUP with wildcards?
Set match_mode to 2. Then you can use * (any characters) and ? (single character) in your lookup value, like =XLOOKUP("*phone*", A2:A100, B2:B100, , 2).
Does XLOOKUP work in Google Sheets?
No. Google Sheets doesn't have XLOOKUP. Use INDEX/MATCH or FILTER for similar lookups.
Can XLOOKUP return an entire row?
Yes. If you set the return_array to span multiple columns (e.g., B2:D100), XLOOKUP returns all corresponding values and spills them into adjacent cells.