XLOOKUP function. Searching for data.(Microsoft Excel)

06/02/2023

Japanese version.

The XLOOKUP function was added to Microsoft 365 in January 2020.
It is a function that integrates the VLOOKUP and HLOOKUP functions and solves problems.

The XLOOKUP function will be the standard in the future due to its greatly improved usability.

It also incorporates the benefits of the INDEX and MATCH functions.

BGM. Music by mubert.com

The XLOOKUP function is available in Excel version 365 and later, and it searches for a value in a table based on a specified key and returns that value. The XLOOKUP function has the following characteristics:

  • Flexibility: It is more flexible than the VLOOKUP or HLOOKUP functions and allows you to specify the range of cells to search.
  • Retrieval of data from multiple columns: Unlike traditional functions that can only retrieve data from a single column, the XLOOKUP function can retrieve data from multiple columns.
  • Approximate and range matching: The XLOOKUP function can perform approximate or range matching in addition to exact matching.
  • Error handling: Instead of returning an error when the search value is not found, you can specify a value to return when an error occurs.

How it works

=XLOOKUP(lookup_value, lookup_array, return_array, if_not_found, match_mode, search_mode)
NameOmissionExplanation
lookup_valueRequired argument. Specify what to search for.
lookup_arrayRequired argument. Specify the range of cells to search.
return_arrayRequired argument. Specify a cell range for search results.
if_not_found#N/ASpecify result if not found.
match_mode0Specify whether to perform an exact match or an ambiguous search.
search_mode10: Exact match.
-1: If an exact match is not found, the closest value that is less than the search value.
1: If an exact match is not found, the closest value that is greater than the search value.
2: If a wildcard is specified, the wildcard value is the closest value to the search value. 2: Specifies when wildcards are specified.

Demonstrate

The parts of the formula that differ from the VLOOKUP function are in red.

Improvements from the VLOOKUP function

Arguments: lookup_value and return_array

While the second argument of the VLOOKUP function specifies the entire table and the third argument specifies the column number to be retrieved, the XLOOKUP function specifies the column to be searched and the result column as a range of cells.

If you specify the range vertically, it becomes a VLOOKUP function, and if horizontally, it becomes a HLOOKUP function.

This eliminates the need for the search range to be the left-most or top-most.
Functions such as obtaining a code from a name can now be easily realized.

The ingenuity that was necessary with the VLOOKUP function is no longer necessary.

Furthermore, if a table item is added because a column number is specified for the retrieved item, the number of the third argument of the VLOOKUP function must be changed, but now the formula of the function is automatically updated when the item is added.

The formula can also be flexibly fixed with an absolute cell reference.

Also, since the results are returned in a cell range, the results can be incorporated into other functions such as the SUM function, and the XLOOKUP function can be nested.
XLOOKUP functions can be nested for cross search.
In addition, images can now be used in search results.

Example of incorporation in SUM function

Calculates the sum of the corresponding rows according to the input values.

Conventionally, the OFFSET and MATCH functions must be combined, or the SUMIF functions must be used to create the total rows in advance.
The XLOOKUP function can be easily implemented.

Other functions that specify a range of cells can also be incorporated.

Example of nested cross search

Two items are specified and results matching both are retrieved.
This is also possible by combining the INDEX and MATCH functions. However, the XLOOKUP function is simpler.

Autofill and copying made easy

By changing the acquisition item from a column number to a range specification (return_array) This makes auto-filling and copy/paste easier and eliminates the need for special handling.

Arguments: if_not_found

The third argument can now be set to a missing case value.
If omitted, it is a #N/A error. Same as the VLOOKUP function.
This allows you to use the ISERROR and IFNA functions without

#N/A error breaks the results of other formulas or The hassle of switching to an arbitrary display is now lightened. This update is useful for the case of blank or unknown if not found.

Arguments: match_mode

This is the biggest improvement to the XLOOKUP function.
The VLOOKUP function often uses FALSE for the third argument.
However, this argument is TRUE when omitted, which is the most inconvenient part.
In the XLOOKUP function, the omitted argument is an exact match.
This reduces the time and effort required to create formulas.
In addition, beginners will not have the trouble of forgetting to specify the search type and making a wrong search.

ValueExplanation
0Exact match.
If omitted, this designation is used.
If not found, #N/A error.However, if if_not_found is specified, that value will be the result.
Wildcards cannot be used.
-1Fuzzy search.
If an exact match is not found, the
the result will be the closest value among the smaller ones.
Wildcards cannot be used.
1Fuzzy search.
If an exact match is not found, the
the result will be the closest value among the larger ones.
Wildcards cannot be used.
2Fuzzy search.
Wildcards are allowed.

Arguments: search_mode

This is an argument that was not in the VLOOKUPfunction.

You can control search results and speed.

ValueExplanation
1Searches are performed in order from the top.
If omitted, this specification is used.
-1Searches are performed sequentially starting from the end.
The VLOOKUP function requires an IF and COUNTIFS functions to obtain the back value matching the search value, but this specification alone makes it possible.
2Binary search based on the assumption that the search range is ordered in ascending order.
-2Binary search based on the assumption that the search range is ordered in descending order.

2 and -2 require the tables to be reordered, but they solve the VLOOKUP function's shortcoming of speed when searching a large table.
The larger the table, the greater the effect of the binary search.

How to use and benefit from Spill

Spill is available in Office365 and Excel 2019 or later.

Example of use for columns

You can omit copying formulas when displaying multiple columns.
To do so, specify a multi-column return range for argument 3.

Although the formula is entered in only the first cell, the The formula is automatically expanded by the width specified by argument 3, without copying cells.

If you need multiple columns but only some of them, hide the columns.

The CHOOSECOLS function, added to Excel in September 2022, can be used to result in multiple columns that are far apart without hiding them.

=CHOOSECOLS( XLOOKUP function ,position of required columns, position of required columns...)

Example of use for lines

Specify argument 1 as a cell range.

Although the formula is entered in only one cell, the formula is self-expanding without copying cells by the height specified by argument 1.

By using spil, in this example, when searching for a product name, you only need to enter it in the H3 cell.
The advantages are as follows. No need to copy-paste after H4.

  • Less input effort. The more cells in a formula, the greater the effect.
  • No risk of forgetting to copy formulas when you edit them. Updating the first cell is reflected in all cells.
  • No need for absolute cell references
  • No need to copy and paste formulas when adding rows in the middle.
  • Less likely to be broken even when deleting rows.

---

Links

Microsoft Excel Functions Lookup & Reference

Examples of Use

How to display Hit if the item exists and Miss if it does not exist using the VLOOKUP function.