totn Excel Functions

MS Excel: How to use the XLOOKUP Function (WS)

This Excel tutorial explains how to use the XLOOKUP function with syntax and examples.

Description

The XLOOKUP function is the next generation lookup function in Excel that has the functionality of both the VLOOKUP function as well as the HLOOKUP function, without the limitations. It performs either a vertical lookup or horizontal lookup by searching for a value in a row or column of a table and returning a corresponding value in a table. Unlike VLOOKUP and HLOOKUP, the XLOOKUP function does not require the lookup value to be in the first column or row of a table, and it can return a default value when a match is not found instead of the #N/A error.

The XLOOKUP function is a built-in function in Excel that is categorized as a Lookup/Reference Function. It can be used as a worksheet function (WS) in Excel. As a worksheet function, the XLOOKUP function can be entered as part of a formula in a cell of a worksheet.

subscribe button Subscribe

If you want to follow along with this tutorial, download the example spreadsheet.

Download Example

Syntax

The syntax for the XLOOKUP function in Microsoft Excel is:

XLOOKUP( value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode] )

Parameters or Arguments

value
The value to search for in the lookup_array.
lookup_array
The array or range of cells to search for value.
return_array
The array or range of cells from which a corresponding value will be returned, based on the position of value in lookup_array.
if_not_found
Optional. The value to return if a match is not found. If this parameter is omitted, the function will return the #N/A error (similar to the VLOOKUP and HLOOKUP functions).
match_mode

Optional. It is the type of match to perform. It can be one of the following values:

match_mode Explanation
0
 Exact match (default)
-1 If no exact match is found, it returns the next smaller item
1 If no exact match is found, it returns the next larger item
2 Wildcard match using the special characters such as:
*, ?, ~
search_mode

Optional. It is the type of search to perform. It can be one of the following values:

search_mode Explanation
1 Search starts at the first item in the lookup_array (default)
-1 Reverse search where the search starts at the last item in the lookup_array
2 Binary search where the items in the lookup_array must be sorted in ascending order (ie: smallest to largest)
-2 Binary search where the items in the lookup_array must be sorted in descending order (ie: largest to smallest)

Returns

The XLOOKUP function performs a lookup and returns a match which can be any datatype such as a string, numeric, date, etc.
If no exact match is found and you specify match_mode as 0 and provide if_not_found parameter, then the XLOOKUP function will return the value provided in the if_not_found parameter.
If no exact match is found and you specify match_mode as 0 and omit if_not_found parameter, then the XLOOKUP function will return #N/A.

Note

Applies To

  • Excel for Office 365, Excel 2019

Type of Function

  • Worksheet function (WS)

Example (as Worksheet Function)

Let's explore how to use XLOOKUP as a worksheet function in Microsoft Excel.

Vertical Lookup Examples

First, let's perform vertical lookups using the XLOOKUP function.

Microsoft Excel

Based on the Excel spreadsheet above, the following XLOOKUP examples would return:

=XLOOKUP("Pears", B2:B6, A2:A6)
Result: 10252

=XLOOKUP("Pears", B2:B6, C2:C6)
Result: $18.60

=XLOOKUP("Pears", B2:B6, D2:D6)
Result: 40

=XLOOKUP("Strawberries", B2:B6, A2:A6)
Result: #N/A         'Returns #N/A error because no exact match is found and a 4th parameter is not provided

=XLOOKUP("Strawberries", B2:B6, A2:A6, "no match")
Result: "no match"   'Returns the value from the 4th parameter because no exact match is found

Horizontal Lookup Examples

Finally, let's perform horizontal lookups using the XLOOKUP function.

Microsoft Excel

Based on the Excel spreadsheet above, the following XLOOKUP examples would return:

=XLOOKUP(10250, B1:F1, B2:F2)
Result: "Grapes"

=XLOOKUP(10250, B1:F1, B3:F3)
Result: $42.30

=XLOOKUP(10250, B1:F1, B4:F4)
Result: 5

=XLOOKUP(10248, B1:F1, B2:F2)
Result: #N/A          'Returns #N/A error because no exact match is found and a 4th parameter is not provided

=XLOOKUP(10248, B1:F1, B2:F2, "not found")
Result: "not found"   'Returns the value from the 4th parameter because no exact match is found
Share on: