Excel’s XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn’t exist, then XLOOKUP can return the closest (approximate) match.
The XLOOKUP function has the power and flexibility of INDEX-MATCH formulas, but it’s easier to use. It’s included in Excel 365 and in versions of Excel after Excel 2019.
Syntax for Excel’s XLOOKUP Function
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )
| lookup_value | Required. The value to search for. (However, if omitted, XLOOKUP searches for blank cells in the lookup_array.) |
| lookup_array | Required. The array or range to search. |
| return_array | Required. The array or range from which to return a value from the corresponding position of the lookup_value found in the lookup_array. |
| [if_not_found] | Optional. Where a valid match is not found, XLOOKUP returns the if_not_found value. Its default value is #N/A. |
| [match_mode] | Optional The match modes are: 0 – Exact match. If none found, return an error value, as specified by the if_not_found setting. This is the default value. -1 – Exact match. If none found, return the next smaller item. 1 – Exact match. If none found, return the next larger item. 2 – A wildcard match where * represents any number of characters, ? represents one character, and ~ (tilda), placed before ? or * searches for the literal value of the character. |
| [search_mode] | Optional. The search modes are: ··· 1 – Perform a search starting at the first item—the default. ··· -1 – Perform a reverse search starting at the last item. ··· 2 – Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned. ··· -2 – Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned. |
Note about search_mode settings of 2 or -2: The benefit of using a binary search is that it’s a faster search method than using a sequential search, which the other search_mode settings use. However, as you’ll see in examples 14-18, using a binary search brings an extra risk. And therefore, because using Excel’s sequential search method is very fast anyway, using a binary search with XLOOKUP is seldom justified.
Examples Formulas Using Excel’s XLOOKUP Function
You can download the example workbook here.
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )
Examples 1 & 2: These two examples compare the INDEX-MATCH and XLOOKUP formulas. As you can see, XLOOKUP’s first two arguments are in the same order as MATCH’s first two arguments. And XLOOKUP’s last argument is the same as INDEX’s first argument.
This probably is the most frequent way you’ll use the XLOOKUP function.
Examples 3-5: These three examples search for an item that’s not in our list. In example 3, XLOOKUP returns the error value we’ve specified, which is zero. In example 4, XLOOKUP returns the next smaller value, because of the match_mode’s argument of -1. And in example 5, XLOOKUP returns the next larger value because of match_mode’s argument of 1.
XLOOKUP is Excel’s first lookup function that can return the next-smaller or next-larger value for unsorted data.
Examples 6-11: XLOOKUP has an unusual definition of “larger” or “smaller.” Normally, those terms are defined with reference to their ASCII codes. But examples 6-8 show three characters with ASCII codes that are far larger than the last letter of the alphabet. (The letter “z” has the ASCII code 122.)
And examples 9-11 show that XLOOKUP treats the first of the three codes as being smaller than “A”. It treats the second code as being larger than “O”. And example 11 treats its character as larger than any item in the list, returning the value of zero for the empty cell A9.
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )
Examples 12-13: Here, we see that case doesn’t matter with XLOOKUP. Both “n” and “N” produce the same result.
Example 14: Although we specified wildcards in cell B25, XLOOKUP didn’t use those wildcards, because the match_mode was set to 1, telling Excel to return the next-higher value.
Example 15: Although this and the remaining formulas in this section use the wildcard match_mode, the value in cell B26 returns an error because its tilda (~) told Excel to look for a literal “*” character. And because there’s no such character in the list, and because the formula specifies no if_not_found value, the formula returned the default #N/A error value.
Examples 16-18: These three formulas show examples of using wildcards.
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )
Examples 19-28: When XLOOKUP uses a search_mode value of 2, the lookup_array (here, the Product range) must be sorted in ascending order. When it’s not, we get unexpected results. The four zeros we see as results for XLOOKUP show that the function is returning either the top or bottom empty cell in the XLOOKUP range.
The five VLOOKUP formulas also expect a sort in ascending order, but they usually return different unexpected results.
Unfortunately, neither XLOOKUP nor VLOOKUP return an error value that alerts us when the lookup_array isn’t sorted properly.
=XLOOKUP( lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )
Example 29: This formula searches for a non-existent value and returns an error because we didn’t specify if_not_found or match_mode settings. The formula therefore used the default values for these settings, which caused #N/A to be returned.
Examples 30-31: These formulas also search for a non-existent value. But because of the formulas’ match_mode settings, example 30 returns the next-highest value and example 31 returns the next lowest value.
Example 32: Because this formula searches a column sorted in ascending order, we can use the search-mode setting of 2. And the match_mode setting of -1 tells the formula to return the next-lower value.
Example 33: Because this formula searches a column sorted in descending order, we can use the search-mode setting of -2. And the match_mode setting of -1 tells the formula to return the next-lower value.
Note: The value of using a binary search is that it’s a faster search method than using a sequential search, which the other search_mode settings do. However, Excel’s sequential search method is so fast that the extra risk of using a binary search is seldom justified.
You can download the examples here.
Some ExcelUser Articles about Excel’s XLOOKUP Function
More Information about Excel’s XLOOKUP Function
- XLOOKUP can return results from a closed external workbook.
- When you search for text with the XLOOKUP function, any numbers in the lookup_array will be ignored. For example, the formula XLOOKUP(123,{5;123},{1;2}) returns 2, but the formula XLOOKUP(“123”,{5;123},{1;2}) returns #N/A.
- XLOOKUP is available to users of Excel 365 and any version after Excel 2019.
- Here’s Microsoft’s help page for the XLOOKUP function.
| Related Functions | |
| VLOOKUP | Looks in the first column of an array and moves across the row to return the value of a cell |
