Excel XLOOKUP Function
last modified October 5, 2026
The XLOOKUP function is Excel's modern replacement for
VLOOKUP and HLOOKUP. It searches a range or array for
a match and returns the corresponding item, so it can look vertically,
horizontally, or in any direction. This tutorial provides a comprehensive guide
to XLOOKUP with detailed examples. You'll learn the basic syntax,
exact and approximate matching, wildcard and reverse searches, left lookups,
spilled array results, and how XLOOKUP compares with
VLOOKUP and INDEX-MATCH.
XLOOKUP Function Basics
The XLOOKUP function searches a range or array for a match and
returns the item corresponding to the first match it finds. If no match exists,
it can return the closest approximate match or a value you supply.
| Component | Description |
|---|---|
| Function Name | XLOOKUP |
| Syntax | =XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) |
| Arguments | 3 required, 3 optional |
| Return Value | Matched value or values from return_array |
This table breaks down the essential components of the XLOOKUP
function. It shows the function name, the complete syntax, the argument
requirements, and the type of value returned.
| Argument | Meaning |
|---|---|
| lookup_value | The value to search for. It can be a number, text, a logical value, or a cell reference. |
| lookup_array | The range or array to search. It must be a single row or a single column. |
| return_array | The range or array to return from. It must be the same size as
lookup_array, otherwise the function returns
#VALUE!. |
| if_not_found | Optional. The value to return when no match is found. If omitted,
#N/A is returned. |
| match_mode | Optional. Controls the match type. The default is 0, an exact match. |
| search_mode | Optional. Controls the search direction. The default is 1, a search from first to last. |
The first three arguments are required and do the main work. The last three are
optional and control error handling, matching, and the order of the search. The
sizes of lookup_array and return_array must match;
each position in one maps to the same position in the other.
Match Modes
The match_mode argument decides how strict a match must be. Unlike
VLOOKUP, whose default is an approximate match,
XLOOKUP defaults to an exact match.
| match_mode | Behaviour | Requires sorted data |
|---|---|---|
| 0 or omitted | Exact match. If none is found, returns #N/A. |
No |
| -1 | Exact match. If none is found, returns the next smaller item. | Yes, ascending |
| 1 | Exact match. If none is found, returns the next larger item. | Yes, ascending |
| 2 | Wildcard match, where *, ?, and
~ have special meaning. |
No |
The default mode 0 is the safest choice for ordinary lookups. Modes -1 and 1 are used for tiered data such as tax brackets and grade scales, where the closest lower or higher threshold is the desired answer. Mode 2 is required before wildcard characters are interpreted; without it, an asterisk is treated as a literal character.
Search Modes
The search_mode argument controls where the search starts and how it
proceeds. It is independent of the match mode.
| search_mode | Behaviour | Requires sorted data |
|---|---|---|
| 1 or omitted | Search from the first item to the last. This is the default. | No |
| -1 | Reverse search, from the last item to the first. Useful for the most recent or last match. | No |
| 2 | Binary search that assumes lookup_array is sorted in
ascending order. |
Yes, ascending |
| -2 | Binary search that assumes lookup_array is sorted in
descending order. |
Yes, descending |
Modes 1 and -1 scan the data, so they work on unsorted ranges. Modes 2 and -2 are binary searches that are faster on very large tables but return invalid results if the data is not sorted as declared. Use -1 to retrieve the last occurrence of a repeated value.
Basic XLOOKUP Example
This example demonstrates the simplest use of XLOOKUP to find a product price.
| A | B | C |
|---|---|---|
| Product | Price | |
| Apple | 1.99 | |
| Banana | 0.99 | |
| Orange | 2.49 | |
| =XLOOKUP("Banana", A2:A4, B2:B4) |
The table shows a product list with prices. The XLOOKUP formula searches for "Banana" in column A and returns the corresponding price from column B.
=XLOOKUP("Banana", A2:A4, B2:B4)
This formula looks for "Banana" in A2:A4 and returns the matching value from
B2:B4. The result will be 0.99. Because the match mode is omitted, XLOOKUP
performs an exact match, and because return_array is a single
column, it returns one value.
XLOOKUP with If Not Found
XLOOKUP lets you specify a custom message when no match is found. This example shows that error-handling feature.
| A | B | C |
|---|---|---|
| Product | Price | |
| Apple | 1.99 | |
| Banana | 0.99 | |
| =XLOOKUP("Pear", A2:A3, B2:B3, "Not found") |
The table demonstrates XLOOKUP's ability to return a custom message when the lookup value does not exist in the lookup array.
=XLOOKUP("Pear", A2:A3, B2:B3, "Not found")
This formula searches for "Pear" in A2:A3. Since it is not found, it returns
"Not found" instead of an error. If if_not_found were omitted, the
formula would return #N/A. Supplying the argument keeps reports
clean when data is missing.
XLOOKUP with Approximate Match
XLOOKUP can perform approximate matches, which is useful for finding the nearest threshold. This example demonstrates a grade lookup based on score ranges.
| A | B | C |
|---|---|---|
| Score | Grade | |
| 0 | F | |
| 60 | D | |
| 70 | C | |
| 80 | B | |
| 90 | A | |
| =XLOOKUP(85, A2:A6, B2:B6, , -1) |
The table shows score ranges and corresponding grades. The XLOOKUP formula finds the closest match at or below 85 and returns the appropriate grade.
=XLOOKUP(85, A2:A6, B2:B6, , -1)
This formula looks for 85 in A2:A6. With match_mode -1, it first tries an exact
match and, finding none, returns the next smaller item (80). The result is "B".
The empty fourth argument leaves if_not_found unset. The lookup
array must be sorted in ascending order for the "next smaller" rule to apply
correctly.
XLOOKUP with Reverse Search
XLOOKUP can search from last to first, which is useful for finding the most recent entry. This example shows finding the latest date for a repeated price.
| A | B | C |
|---|---|---|
| Date | Price | |
| 1/1/2023 | 1.99 | |
| 2/1/2023 | 2.19 | |
| 3/1/2023 | 1.99 | |
| =XLOOKUP(1.99, B2:B4, A2:A4, , 0, -1) |
The table contains dated price entries. The XLOOKUP formula searches from bottom to top to find the most recent occurrence of 1.99.
=XLOOKUP(1.99, B2:B4, A2:A4, , 0, -1)
This formula searches for 1.99 in B2:B4 from bottom to top. The value 1.99 appears in both the first and third rows, and the reverse search returns the last occurrence, so the result is the date 3/1/2023. This is valuable for time-series data, where the newest matching record is usually the one that matters.
XLOOKUP with Wildcards
XLOOKUP supports wildcard characters for partial matches when
match_mode is 2. An asterisk matches any sequence of characters,
and a question mark matches any single character.
| A | B | C |
|---|---|---|
| Product | Code | |
| Apple iPhone | APL-001 | |
| Samsung Galaxy | SAM-002 | |
| Google Pixel | GGL-003 | |
| =XLOOKUP("*Pixel*", A2:A4, B2:B4, , 2) |
The table shows product names and codes. The XLOOKUP formula uses wildcards to find any product containing "Pixel" and return its code.
=XLOOKUP("*Pixel*", A2:A4, B2:B4, , 2)
This formula searches for any product in A2:A4 containing "Pixel". The
match_mode value 2 enables wildcard interpretation, and the result
is "GGL-003". To find a literal asterisk or question mark, escape it with a
tilde, for example "~*". Note that wildcards require mode 2; with
the default exact match they are treated as ordinary text.
XLOOKUP Left Lookup
Unlike VLOOKUP, which can only return values to the right of the
key, XLOOKUP can return values from either side because the lookup
and return ranges are given separately.
| A | B | C |
|---|---|---|
| Name | ID | |
| John | 101 | |
| Sarah | 102 | |
| Mike | 103 | |
| =XLOOKUP(102, B2:B4, A2:A4) |
The table lists names in column A and IDs in column B. The formula finds the name for ID 102, looking left from the ID column.
=XLOOKUP(102, B2:B4, A2:A4)
This formula searches for 102 in B2:B4 and returns the corresponding value from
A2:A4, which lies to the left of the lookup column. The result is "Sarah". The
same task with VLOOKUP would fail, because the key would have to be
the first column of the range.
Returning Multiple Columns
The return_array can span more than one column. In that case
XLOOKUP returns an array of values that spills into the neighboring
cells.
| A | B | C | D |
|---|---|---|---|
| ID | Name | Dept | Salary |
| 101 | John | IT | 75000 |
| 102 | Sarah | HR | 68000 |
| 103 | Mike | Sales | 72000 |
| =XLOOKUP(102, A2:A4, B2:D4) |
The table contains employee records. The formula looks up ID 102 and returns three related fields at once.
=XLOOKUP(102, A2:A4, B2:D4)
Here lookup_array is the single column A2:A4 and
return_array is the three-column range B2:D4. The formula returns
"Sarah", "HR", and 68000, spilling across three cells. This single-formula
retrieval is impossible with VLOOKUP, which returns only one
column per call.
XLOOKUP vs VLOOKUP vs INDEX-MATCH
All three tools can look up data, but they differ in direction, safety, and availability. The table below summarizes the trade-offs.
| Feature | XLOOKUP | VLOOKUP | INDEX-MATCH |
|---|---|---|---|
| Lookup direction | Any direction | Right only | Any direction |
| Safe when columns are inserted | Yes | No, the column index breaks | Yes |
| Exact match by default | Yes | No, pass FALSE | No, pass 0 |
| Not-found handling | if_not_found argument | #N/A, wrap in IFERROR | #N/A, wrap in IFNA |
| Return several columns | Yes, spills | No | Yes, with an array |
| Reverse search | Yes, search_mode -1 | No | No |
| Availability | Excel 2021 and 365 | All versions | All versions |
For example, the same result as
=VLOOKUP("P101", A2:C4, 3, FALSE) can be written as
=INDEX(C2:C4, MATCH("P101", A2:A4, 0)) or
=XLOOKUP("P101", A2:A4, C2:C4). XLOOKUP is the most
convenient of the three because it is exact by default, looks in any direction,
returns several columns at once, and has a built-in argument for values that are
not found. The trade-off is availability: XLOOKUP requires
Microsoft 365 or Excel 2021 and later, while VLOOKUP and
INDEX-MATCH work in every version.
XLOOKUP Errors and Troubleshooting
| Cause | Symptom | Fix |
|---|---|---|
| Lookup value does not exist | #N/A error | Supply the if_not_found argument or check the data |
| lookup_array and return_array are different sizes | #VALUE! error | Make both ranges the same number of rows or columns |
| XLOOKUP used in Excel 2019 or earlier | #NAME? error | Use INDEX-MATCH or VLOOKUP, or upgrade to
Excel 2021 or 365 |
| Wildcards used without mode 2 | #N/A or a literal match | Set match_mode to 2 |
| Approximate match on unsorted data | A wrong but plausible value | Sort the lookup array ascending, or use an exact match |
| Binary search (search_mode 2 or -2) on unsorted data | Invalid or surprising results | Sort the array as declared, or use search_mode 1 |
| Numbers stored as text (or the reverse) | #N/A even though the value looks present | Make both sides the same type, for example with VALUE |
| Returned array has nowhere to spill | #SPILL! error | Clear the cells blocking the spill range |
The XLOOKUP function is the most flexible lookup tool in modern
Excel. It replaces VLOOKUP and HLOOKUP with a simpler
syntax, returns exact matches by default, looks in any direction, retrieves
whole rows or columns as a spilled array, and handles missing values with a
single argument. From simple price lookups to tiered grade scales, wildcard
searches, and reverse searches for the latest record, it covers the tasks that
once required several functions. Remember that it needs Microsoft 365 or Excel
2021 and later, that lookup_array and return_array
must be the same size, and that approximate and binary searches demand
correctly sorted data. Mastering XLOOKUP will make your
spreadsheets more reliable and your lookups far more flexible.
Author
List all Excel Formulas.