XLOOKUP finds a value in one range and returns the corresponding value from another, making many common lookup tasks simpler than VLOOKUP.
This guide shows the formula’s syntax, practical examples, compatibility limits, and ways to handle errors and edge cases.
What does XLOOKUP do?
XLOOKUP searches a lookup array for a value, then returns a value from the corresponding position in a return array.
For example, it can find a product code in column A and return its price from column C.
Unlike VLOOKUP, the return column does not need to be to the right of the lookup column, and you do not need to count a column index number.
By default, XLOOKUP looks for an exact match and searches from the first item to the last.
If it finds no match, it returns an error unless you provide a custom not-found result.
XLOOKUP syntax and arguments
The basic syntax is:
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])lookup_value: The value to find, such as a product ID in E2.lookup_array: The one-row or one-column range to search, such as A2:A100.return_array: The corresponding one-row or one-column range to return from, such as C2:C100.if_not_found: Optional text or value to return when there is no match.match_mode: Optional setting for exact, approximate, or wildcard matching.search_mode: Optional setting for search direction or binary search.
The lookup and return arrays should have compatible dimensions.
If you search a single column, return a single column with the same number of rows.
A mismatch can cause a calculation error or produce unexpected results.
How do you use XLOOKUP for an exact match?
Suppose a worksheet has product IDs in A2:A6 and prices in C2:C6.
Cell E2 contains the ID to look up.
Use:
=XLOOKUP(E2,A2:A6,C2:C6,"Product not found")If E2 contains an ID from column A, the formula returns the price from the same row in column C.
If it is absent, the result is Product not found.
The fourth argument is optional, but adding it makes missing results clearer than displaying #N/A.
You can also look to the left.
If product IDs are in C2:C6 and names are in A2:A6, use:
=XLOOKUP(E2,C2:C6,A2:A6,"ID not found")This flexibility is useful when a table’s layout changes or the return data is not positioned to the right of the lookup column.
How do you return multiple columns?
In Excel versions that support dynamic arrays, the return array can contain multiple columns.
For example, with IDs in A2:A100, names in B2:B100, and prices in C2:C100, this formula returns both the name and price:
=XLOOKUP(E2,A2:A100,B2:C100,"Product not found")The results spill into adjacent cells.
Leave those cells empty; otherwise Excel may show a #SPILL! error.
If the formula is inside an Excel Table, spilling behavior may be restricted, so place it in the worksheet grid outside the table or return one column at a time.
How do approximate matches work?
Set match_mode to -1 to find an exact match or the next smaller item, or 1 to find an exact match or the next larger item.
For example, if A2:A5 contains ascending minimum scores (0, 60, 70, 80) and B2:B5 contains grade labels, this returns the grade for the largest minimum score not exceeding the score in E2:
=XLOOKUP(E2,A2:A5,B2:B5,"No grade",-1)For this threshold pattern, keep the minimum values sorted in ascending order.
Approximate matching is useful for rate bands and score thresholds, but unsorted data can make the intended result difficult to verify.
Can XLOOKUP search from the last match?
Set search_mode to -1 to search from the bottom upward.
This returns the most recent matching record when dates or transactions are listed in chronological order:
=XLOOKUP(E2,A2:A100,C2:C100,"No record",0,-1)Here, 0 explicitly requests an exact match, and -1 sets the reverse search direction.
If duplicate lookup values exist, this setting returns the last one encountered rather than the first.
How do you use wildcards with XLOOKUP?
Set match_mode to 2 to use wildcards.
The asterisk matches any number of characters, while the question mark matches one character.
For example, to find the first name in A2:A100 that begins with the text in E2, use:
=XLOOKUP(E2&"*",A2:A100,B2:B100,"No match",2)If E2 contains Sam, the formula can match a name such as Samuel.
Wildcard searches are useful for partial text, but they can return an unintended result if several entries share the same starting characters.
How can you troubleshoot common XLOOKUP errors?
#N/A: The lookup value was not found.
Check for spelling differences, extra spaces, or mismatched data types.
Use the optionalif_not_foundargument to show a helpful message.#VALUE!: Confirm that the lookup and return arrays have compatible dimensions and are valid ranges.#SPILL!: Clear cells blocking a multi-column result, or move the formula outside a table.- Unexpected match: Check whether you selected an approximate or wildcard match mode, and whether duplicate values cause the first or last match to be returned.
Numbers stored as text are a frequent cause of missing matches.
A numeric 123 may not match the text value "123".
Standardize the source data or convert values consistently before relying on the lookup.
For text values, leading or trailing spaces can also interfere; TRIM can help clean data when applied to a helper column.
Which Excel versions support XLOOKUP?
XLOOKUP is available in Microsoft 365 and supported newer versions of Excel, including Excel 2021 and Excel 2024.
It is not available in Excel 2019 or Excel 2016 as a built-in function.
A workbook containing XLOOKUP may show #NAME? when opened in an unsupported version.
If you need to share a workbook with users on older Excel releases, consider a compatible alternative such as INDEX with MATCH or VLOOKUP, depending on the table layout.
Google Sheets also supports an XLOOKUP function, but function availability and behavior can depend on the spreadsheet environment.
When sharing formulas across Excel and Sheets, check that the recipient’s application supports the function and review any differences in separators, locale settings, or array behavior.