- 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.
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])
|The lookup value|
|The array or range to search|
|The array or range to return|
|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.
|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.
|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.
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.
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)
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.
This example incorporates the if_not_found argument to the example above.
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.
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.
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.
The formula in cells D3:F3 is: =XLOOKUP(D2,$B6:$B17,XLOOKUP($C3,$C5:$G5,$C6:$G17)).
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.
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.