XLOOKUP function

Use the XLOOKUPfunction when you need to find things 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. With XLOOKUP, you can look in one column for a search term, and return a result from the same row in another column, regardless of which side the return column is on.

In this example, we're looking up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP is able to return an array with multiple items, which allows a single formula to return both employee name and department.

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: August 28, 2019: XLOOKUP is currently a beta feature, and only available to a portion of Office Insiders at this time. We'll continue to optimize it over the next several months. When XLOOKUP is ready, we'll release it to all Office Insiders, and Office 365 subscribers.

The XLOOKUP function searches a range or an array, and returns an item corresponding to the first match it finds. If a match doesn't exist, then XLOOKUP can return the closest (approximate) match. 

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

Argument

Description

lookup_value

Required

The lookup value

lookup_array

Required

The array or range to search

return_array

Required

The array or range to return

[match_mode]

Optional

Specify the match type:

0 - Exact match. If none found, return #N/A. This is the default.

-1 - Exact match. If none found, return the next smaller item.

1 - Exact match. If none found, return the next larger item.

2 - A wildcard match where *, ?, and ~ have special meaning.

[search_mode]

Optional

Specify the search mode to use:

1 - Perform a search starting at the first item. This is the default.

-1 - Perform a reverse search starting at the last item.

2 - Perform a binary search that relies on lookup_array being sorted in ascending order. If not sorted, invalid results will be returned.

-2 - Perform a binary search that relies on lookup_array being sorted in descending order. If not sorted, invalid results will be returned.

Examples

Example 1

The following example uses a simple XLOOKUP to look up a country name, and return its telephone country code. It only includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It does not include the match_mode argument, as it defaults to an exact match.

Image of the XLOOKUP function used to return a country dial code from a table.

Note: XLOOKUP is different 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

The following example looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It uses the match_mode argument set to 1, which means the function will look for an exact match, and if it can't find one, it will return the next larger item.

Image of the XLOOKUP function used to return a tax rate based on maximum income. This is an approximate match.

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 3

Next, we'll use a nested XLOOKUP function to perform both a vertical and horizontal match. In this case, it will first look for Gross Profit in column B, then look for Qtr1 in the top row of the table (range C5:F5), and return the value at the intersection of the two. This is similar to using the INDEX and MATCH functions in conjunction. You can also use XLOOKUP to replace the HLOOKUP function.

Image of the XLOOKUP function used to return horizontal data from a table by nesting 2 XLOOKUPs.

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

Example 4

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

Image of using nested XLOOKUPs with SUM to add the values between start and end points.

The formula in cell E3 is: =SUM(XLOOKUP(C3,C6:C10,F6:F10):XLOOKUP(D3,C6:C10,F6:F10))

How does it work? XLOOKUP returns a range, so when it calculates, the formula ends up looking like this: =SUM($F$7:$F$9). You can see how this works on your own by selecting a cell with an XLOOKUP formula similar to this one, then go to Formulas > Formula Auditing > Evaluate Formula, and press the Evaluate button to step 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

Expand your Office skills
Explore training
Got It
Get instant Excel help
Connect to an expert now
Subject to Got It terms and conditions

Was this information helpful?

Thank you for your feedback!

Thank you for your feedback! It sounds like it might be helpful to connect you to one of our Office support agents.

×