XLOOKUP function - Microsoft Support (2024)

Table of Contents
Syntax Examples See also FAQs References

Use the XLOOKUP function to find things in a table orrange by row. For example, look up theprice 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.

Note:XLOOKUP is not available in Excel 2016 and Excel 2019, however, you may come across a situation of using a workbook in Excel 2016 or Excel 2019 with the XLOOKUP function in it created by someone else using a newer version of Excel.

XLOOKUP function - Microsoft Support (1)

Syntax

TheXLOOKUPfunction searches a range or an array, and then returns the itemcorrespondingto thefirst match it finds. If no match exists,then XLOOKUP can return theclosest (approximate) match.

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

Argument

Description

lookup_value

Required*

The value to search for

*If omitted, XLOOKUP returns blank cells it finds in lookup_array.

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 not found, return the [if_not_found] text you supply.

If a valid match is not found, and [if_not_found] is missing, #N/A is returned.

[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 1uses XLOOKUP to look up a country name in a range, and then return its telephone country code. It includes the lookup_value (cell F2), lookup_array (range B2:B11), and return_array (range D2:D11) arguments. It doesn't include the match_mode argument, as XLOOKUP produces an exact match by default.

XLOOKUP function - Microsoft Support (2)

Note:XLOOKUP uses a lookup arrayand a return array, whereas 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 2looks up employee information based on an employee ID number. Unlike VLOOKUP, XLOOKUP can return an array with multiple items, soa single formula can return both employee name and department from cells C5:D14.

XLOOKUP function - Microsoft Support (3)

———————————————————————————

Example 3adds anif_not_found argument to the preceding example.

XLOOKUP function - Microsoft Support (4)

———————————————————————————

Example 4looks in column C for the personal income entered in cell E2, and finds a matching tax rate in column B. It sets the if_not_found argument to return0 (zero) if nothing is found. The match_mode argument is set to 1, which means the function will look for an exact match, and if it can't find one, it returns the next larger item. Finally, the search_mode argument is set to 1, which means the function will search from the first item to the last.

XLOOKUP function - Microsoft Support (5)

Note:XARRAY's lookup_array column is to the right of the return_array column, whereas VLOOKUP can only look from left-to-right.

———————————————————————————

Example 5uses a nested XLOOKUP function to perform both a vertical and horizontal match. It first looks for Gross Profit in column B, then looks for Qtr1 in the top row of the table (range C5:F5), and finally returns the value at the intersection of the two. This is similar to using the INDEX and MATCH functions together.

Tip:You can also use XLOOKUP to replace the HLOOKUP function.

XLOOKUP function - Microsoft Support (6)

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

———————————————————————————

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

XLOOKUP function - Microsoft Support (7)

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 ends up looking like this: =SUM($E$7:$E$9). You can see how this works on your own by selecting a cell with an XLOOKUP formula similar to this one, then selectFormulas > Formula Auditing > Evaluate Formula, and then select Evaluateto step through the calculation.

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

———————————————————————————

See also

You can always ask an expert in the Excel Tech Communityor get support inCommunities.

XMATCH function

Excel functions (alphabetical)

Excel functions (by category)

XLOOKUP function - Microsoft Support (2024)

FAQs

What are the limitations of Xlookup function? ›

Some of the drawbacks of XLOOKUP are:
  • It is not compatible with older versions of Excel 2019 and lower.
  • It may not be as flexible as INDEX MATCH for some scenarios, such as returning values from non-contiguous columns.
  • It may not work well with large data sets or volatile formulas.
Jan 24, 2024

Why is XLOOKUP not working properly? ›

XLOOKUP returns #VALUE error

error occurs if the lookup and return arrays have incompatible dimensions. For instance, it is not possible to search in a horizontal array and return values from a vertical array.

What is the Xlookup function support? ›

The XLOOKUP function searches a range or an array, and then returns the item corresponding to the first match it finds. If no match exists, then XLOOKUP can return the closest (approximate) match. *If omitted, XLOOKUP returns blank cells it finds in lookup_array.

Why don't people use xlookup? ›

The first reason has to do with compatibility. XLOOKUP is available on Microsoft 365 and Excel 2021. That means if your users are on Excel 2019 or earlier, XLOOKUP is not an option for them.

Why VLOOKUP is better than XLOOKUP? ›

Making the Choice between VLOOKUP vs XLOOKUP

XLOOKUP is more flexible and powerful, but it's only available in Excel 2021 (and above) and Microsoft 365. While it has its limitations, VLOOKUP is still useful, especially if you're working with older Excel versions (Excel 2019 and below) or need something simpler.

Is Xlookup more efficient than index match? ›

Final Thoughts on XLOOKUP vs. INDEX MATCH. XLOOKUP has emerged as the ultimate winner in Excel-based lookup formulas. Moreover, its flexibility and efficiency make it a great choice for those who require to quickly locate data, compare data, or perform lookups between multiple tables.

Why does xlookup return spill? ›

This error occurs when the spill range for a spilled array formula isn't blank. Select the formula to reveal a dashed border that indicates the intended spill range.

Why does XLOOKUP return 0 instead of value? ›

VLOOKUP and XLOOKUP will return 0 if the lookup value is found in the lookup column, but the corresponding cell in the return column is blank.

Why my lookup formula is not working? ›

The lookup column is not the leftmost column of the table array. One of the most significant limitations of Excel VLOOKUP is that it cannot look to its left. Consequently, a lookup column should always be the leftmost column in the table array. In practice, we often forget about this and end up with #N/A errors.

What is the alternative function to Xlookup? ›

The FILTER function is one of the easiest ways to replicate XLOOKUP in Google Sheets. Here's the basic FILTER function syntax. Specify the range containing the value you want to search or return (C3:C11). Then add the criteria range (A3:A11) and criteria (F3) to your formula.

What version of Excel do I need for Xlookup? ›

If you have confirmed that you are using a version of Microsoft Excel that should support the XLOOKUP function (Excel 2019 or later, including Excel for Microsoft 365), and you are still experiencing issues, there are a few additional steps you can take: 1.

How to use xlookup to find matching data? ›

For instance, in the formula =XLOOKUP(E3,A2:A10,D2:D10,”not found”,-1,1), Excel finds the exact match or next and smallest value matching the lookup value in cell E2 and returns it. With search_mode set as 1, Excel will search in ascending order, returning the first matching value in the range.

What is the downside to Xlookup? ›

Disadvantages of XLOOKUP

Just like VLOOKUP, XLOOKUP can only return the first match it finds. This can cause problems if you have records with the same data in the lookup array.

What is the problem with Xlookup? ›

error in your XLOOKUP function, the most likely reason is that your lookup array and your return array are not the same size. In my example, the reason the arrays are different sizes is that there is a blank cell at the end of my return array.

What are the limitations of xlookup? ›

Limitations
  • Unique criteria combinations. You need to have a unique combination of conditions for your lookup values, otherwise XLOOKUP will return an error or the first match.
  • Consistent array dimensions. The lookup and return arrays must have the same number of rows or columns.
Jan 10, 2024

What are the limitations of the lookup function? ›

The only limitation of the LOOKUP Function is that it's not completely dynamic. If, for example, you wanted to be able to change the output column from Age to Country, you'd need to manually change the formula to refer to a different column.

What is the range limit for Xlookup? ›

But XLOOKUP imposes no limit on the lookup value's size.

Do xlookups slow down Excel? ›

Because calculation times for VLOOKUP and INDEX/MATCH are on a similar level, the performance of XLOOKUP compared to INDEX/MATCH doesn't surprise much: XLOOKUP is significantly slower than INDEX/MATCH as well.

How many rows can xlookup handle? ›

The Xlookup function doesn't have a limit. This means you can use all 1,048,576 rows and 16,384 columns of a workbook.

References

Top Articles
Latest Posts
Recommended Articles
Article information

Author: Carmelo Roob

Last Updated:

Views: 5508

Rating: 4.4 / 5 (45 voted)

Reviews: 84% of readers found this page helpful

Author information

Name: Carmelo Roob

Birthday: 1995-01-09

Address: Apt. 915 481 Sipes Cliff, New Gonzalobury, CO 80176

Phone: +6773780339780

Job: Sales Executive

Hobby: Gaming, Jogging, Rugby, Video gaming, Handball, Ice skating, Web surfing

Introduction: My name is Carmelo Roob, I am a modern, handsome, delightful, comfortable, attractive, vast, good person who loves writing and wants to share my knowledge and understanding with you.