XLOOKUP function

  • Applies to: Excel for Microsoft 365, Excel for Microsoft 365 for Mac, Excel for the web, Excel for iPad, Excel for iPhone, Excel for Android tablets, Excel for Android phones.

Use the XLOOKUP function anytime you have to track down specific data in a table or a range by row. For example, look up the price of an automotive part by the part number, or find an employee name based on their employee ID. Using XLOOKUP allows you to look in one column for a search term, and capture a result from the same row in another column, irrespective of which side the return column is on.

Note: This function is currently available to Microsoft 365 subscribers in Current Channel. It will be available to Microsoft 365 subscribers in Semi-Annual Enterprise Channel starting in July 2020. For more information on how features are rolled out to Microsoft 365 subscribers, see When do I get the newest features for Microsoft 365.

Syntax

The XLOOKUP function scans a range or an array, and returns an item associated to the first match it detects. If a match doesn’t exist, then XLOOKUP can yield the closest (approximate) match. 

=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode]) 

ArgumentDescription
lookup_value
Required
The lookup value
lookup_array
Required
The array or range to search
return_array
Required
The array or range to return
[if_not_found]
Optional
Where a valid match is undiscovered, capture the [if_not_found] text you provide.
If a valid match is not located, and [if_not_found] is absent, #N/A will be returned.
[match_mode]
Optional
Specify the match type:
0 – Exact match. If none found, yield #N/A. This is the default.
-1 – Exact match. If none found, give the next smaller item.
1 – Exact match. If none found, yield the next larger item.
2 – A wildcard match where *, ?, and ~ have special meaning.
[search_mode]
Optional
Define the search mode to use:
1 – Run a search beginning at the first item. This is the standard.
-1 – Conduct a reverse search commencing at the last item.
2 – Run a binary search that depends on lookup_array being arranged in ascending order. If left unarranged, invalid results will be captured.
-2 – Proces a binary search that is based on lookup_array being sorted in descending order. If unsorted, invalid results will be captured.

Examples

Example 1

This example is from the video above, and uses a simple XLOOKUP to look up a country name, then bring back its telephone country code. It only comprises the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It excludes the match_mode argument, as XLOOKUP defaults to an exact match.Example of the XLOOKUP function used to return an Employee Name and Department based on Employee ID. The formula is =XLOOKUP(B2,B5:B14,C5:C14).

Note: XLOOKUP varies from VLOOKUP in that it uses separate lookup and return arrays, where VLOOKUP uses a single table array followed by a column index number. The equivalent VLOOKUP formula in this case would be: =VLOOKUP(F2,B2:D11,3,FALSE)

Example 2

In this example, we’re looking up employee information according to an employee ID number. Unlike VLOOKUP, XLOOKUP can return an array with several items, which enables one formula to return both employee name and department from cells C5:D14.

Example of the XLOOKUP function used to return an Employee Name and Department based on Employee IDt. The formula is: =XLOOKUP(B2,B5:B14,C5:D14,0,1)

Example 3

This example incorporates the if_not_found argument to the example above.

Example of the XLOOKUP function used to return an Employee Name and Department based on Employee ID with the if_not_found argument. The formula is =XLOOKUP(B2,B5:B14,C5:D14,0,1,"Employee not found")

Example 4

The following example looks in column C for the personal income typed in cell E2, and locates a matching tax rate in column B. It sets the if-not_found argument to return a 0 if nothing is found. The match_mode argument is set to 1, meaning that the function will look for an exact match, and if it can’t find one, it will yield the next larger item. Finally, the search_mode argument is set to 1, which indicates the function will search from the initial item to the last.

Image of the XLOOKUP function used to return a tax rate based on maximum income. This is an approximate match.The formula is: =XLOOKUP(E2,C2:C7,B2:B7,1,1)

Note: Unlike VLOOKUP, the lookup_array column is to the right of the return_array column, where VLOOKUP can only look from left-to-right.

Example 5

Next, we’ll use a nested XLOOKUP function to run both a vertical and horizontal match. In this case, it will start to look for Gross Profit in column B, then look for Qtr1 in the top row of the table (range C5:F5), and secure the value at the intersection of the two. This is like using the INDEX and MATCH functions together. You can even use XLOOKUP to substitute the HLOOKUP function.

Image of the XLOOKUP function used to return horizontal data from a table by nesting 2 XLOOKUPs. The formula is: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17))

The formula in cells D3:F3 is: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)).

Example 6

This example uses the SUM function, and two XLOOKUP functions nested together to sum all the values across two ranges. In this situation, we want to sum the values for grapes, bananas, and include pears, which are amid the two.

Using XLOOKUP with SUM to total a range of values that fall between two selections

The formula in cell E3 is: =SUM(XLOOKUP(B3,B6:B10,E6:E10):XLOOKUP(C3,B6:B10,E6:E10))

How does it work? XLOOKUP returns a range, so when it calculates, the formula ultimately resembles this: =SUM($E$7:$E$9). You can notice how this works on your own by picking a cell with an XLOOKUP formula alike this one, then navigate your way to Formulas > Formula Auditing > Evaluate Formula, and select the Evaluate button to pass through the calculation.

Note: Thanks to Microsoft Excel MVP, Bill Jelen, for suggesting this example.

Need more help?

You can always ask an expert in the Excel Tech Community, get support in the Answers community, or suggest a new feature or improvement on Excel User Voice.

See Also

XMATCH function

Excel functions (alphabetical)

Excel functions (by category)

Leave a Reply

%d bloggers like this: