Excel XLOOKUP function is a powerful and flexible lookup function introduced in Excel 365 that allows you to search for a value in a table or range and return a corresponding result from the same or another table. XLOOKUP offers several advantages over traditional lookup functions such as VLOOKUP and HLOOKUP, including the ability to search in any column or row, search for exact matches or approximate matches, and handle errors more gracefully.
XLOOKUP Function Summary
The XLOOKUP function is a powerful and flexible Excel function designed to replace outdated lookup functions like VLOOKUP, HLOOKUP, and LOOKUP. With support for approximate and exact matching, wildcards for partial matches, and lookups in both vertical and horizontal ranges, XLOOKUP streamlines your formulas and makes your work easier. This versatile function is only available to Microsoft 365 (formerly Office 365) users.
XLOOKUP Function Purpose
The purpose of the XLOOKUP function is to search for a value in a table or range and return a corresponding result from the same or another table. It is designed to provide a more flexible and efficient way of performing lookup tasks compared to traditional lookup functions.
XLOOKUP Function Arguments
The XLOOKUP function has several arguments:
- lookup_value: The value that you want to search for in the table or range.
- lookup_array: The range of cells that you want to search in.
- return_array: The range of cells from which you want to return a corresponding result.
- [match_mode]: Optional argument that specifies whether to search for an exact match or an approximate match. It can be set to 0 (exact match) or 1 (approximate match).
- [search_mode]: Optional argument that specifies the direction of the search. It can be set to 1 (search from top to bottom) or -1 (search from bottom to top).
- [if_not_found]: Optional argument that specifies the value to return if the lookup_value is not found.
XLOOKUP Function Return Value
The XLOOKUP function returns a value from the same or another table based on the lookup criteria specified in the function.
XLOOKUP Function Syntax
=XLOOKUP(lookup_value, lookup_array, return_array, [match_mode], [search_mode], [if_not_found])XLOOKUP Match Type
| Match Type | Description |
|---|---|
| 0 or Exact Match | Looks for an exact match in the lookup array. If a match is found, it returns the corresponding value from the return array. If no match is found, it returns an #N/A error. |
| -1 or Next Smallest | Looks for the smallest value in the lookup array that is greater than or equal to the lookup value. If a match is found, it returns the corresponding value from the return array. If no match is found, it returns the value that is immediately smaller than the lookup value. |
| 1 or Next Largest | Looks for the largest value in the lookup array that is less than or equal to the lookup value. If a match is found, it returns the corresponding value from the return array. If no match is found, it returns the value that is immediately larger than the lookup value. |
| 2 or Wildcard Match | Looks for a match using wildcard characters (* and ?) in the lookup value. If a match is found, it returns the corresponding value from the return array. If no match is found, it returns an #N/A error. |
Note that the Match type argument is optional in XLOOKUP, and if it is omitted, the default value is 0 or Exact Match.
XLOOKUP Search Mode
The XLOOKUP function begins matching from the first data value by default. However, you can control the search behavior using the optional argument called “search_mode,” which offers several options:
| Search Mode | Description |
|---|---|
| 1 (Exact match) | XLOOKUP will only return a result if the lookup value exactly matches a value in the search array. |
| -1 (Exact match or next smaller item) | If the lookup value is not found in the search array, XLOOKUP will return the next smaller item. |
| 2 (Wildcard match) | XLOOKUP will allow the use of wildcards (* and ?) in the lookup value to find approximate matches. |
| -2 (Wildcard match or next smaller item) | If the lookup value is not found in the search array, XLOOKUP will use wildcards (* and ?) to find the next smaller item. |
| 3 (Binary search) | XLOOKUP will use a binary search algorithm to find the lookup value in the search array, which is faster for larger arrays. |
| -3 (Binary search or next smaller item) | If the lookup value is not found in the search array, XLOOKUP will use binary search to find the next smaller item. |
Excel XLOOKUP Function Examples
The Excel XLOOKUP function is a powerful tool for searching and retrieving data from a table. Here are some examples of how you can use XLOOKUP
Basic XLOOKUP
This example shows how to use XLOOKUP to search for a value in a table and return a corresponding result.
=XLOOKUP("Banana", A1:A5, B1:B5)
In this example, the function searches for the value “Banana” in the range A1:A5 and returns the corresponding result from the range B1:B5.
In our example above, it will return the value 0.50, as “Banana” is found in the first column and its corresponding value in the second column is 0.50.
XLOOKUP with Approximate Match
This example shows how to use XLOOKUP with approximate match mode to search for a value that is not an exact match.
=XLOOKUP(70, A1:A5, B1:B5, 1)
In this example, the function searches for the value 70 in the range A1:A5 and returns the corresponding result from the range B1:B5 using approximate match mode.
In our example, it will return the value 3.0, as 70 is found in the first column and its corresponding value in the second column is 3.0.
XLOOKUP with If Not Found Value
This example shows how to use XLOOKUP with the if_not_found argument to return a specified value if the lookup value is not found.
=XLOOKUP("Orange", A1:A5, B1:B5, , , "Not Found")In this example, the function searches for the value “Orange” in the range A1:A5 and returns the corresponding result from the range B1:B5. If the lookup value is not found, the function returns the value “Not Found”.
XLOOKUP Multiple Values
XLOOKUP has the ability to return multiple values for a single match. For instance, the following example illustrates how XLOOKUP can be set up to return three matching values with a single formula.
In cell B7, the formula used is:
=XLOOKUP(A7,A1:A4,B1:D4)
Observe that the range specified in the return_array argument of the XLOOKUP function includes three columns: Region, Cost, and Sales. Therefore, all three values are returned and spilled into the range B7:D7.
So, in our example above, cell A7 contains the value 20, the function returns a value of 2.7, 2.9, 2.6 spilled into the range B7:D7
XLOOKUP Two-Way Lookup Example
Nested XLOOKUPs can be used to perform a two-way lookup in Excel. The inner XLOOKUP retrieves a row of data, which is then used as the return array for the outer XLOOKUP.
Let’s use the following function:
=XLOOKUP(G4,B1:E1,XLOOKUP(G3,A2:A5,B2:E5))
If cell G3 contains a value that is found in the range A2:A5, then the second XLOOKUP function will return the corresponding row from the range B2:E5.
For example, if cell G3 contains the value 30, then the second XLOOKUP function will return the row {0.30, 0.25, 0.20, 0.15}.
The first XLOOKUP function will then search for a value in cell G4 within the range B1:E1 and return the corresponding value from the row returned by the second XLOOKUP function.
For example, if cell G4 contains the value “25-34”, then the first XLOOKUP function will return the value 0.25.
As you can see in our example above,
XLOOKUP Benefits
- Can perform exact and approximate matches, as well as search in any direction (horizontally or vertically)
- Can handle multiple search criteria and return corresponding results from multiple columns
- Can handle errors and provide customizable error messages
- More flexible and easier to use compared to other lookup functions like VLOOKUP and HLOOKUP
- Can save time and effort by eliminating the need for nested IF statements and other workarounds.
Excel XLOOKUP Function Notes
- The XLOOKUP function is only available in Excel 365 and later versions.
- When using XLOOKUP with approximate match mode, the lookup_array must be sorted in ascending order.
- The XLOOKUP function can handle errors more gracefully than traditional lookup functions, such as VLOOKUP and HLOOKUP.