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)


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])





The value to search for

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



The array or range to search



The array or range to return



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.



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.



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.


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)


Does Office 365 support xlookup? ›

This function doesn't return a cell reference as a value but as a result. You'll get a #NAME error if looking for XLOOKUP in an Excel version that doesn't support it (It's only available on Microsoft 365 Excel, Excel 2021 and later).

What happened to Xlookup in Excel? ›

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.

Why does the Xlookup function not work? ›

error will occur if there are not enough empty cells. Note. If your lookup array contains multiple occurrences of the lookup value and you aim to return all matches, XLOOKUP cannot do that.

What is the Xlookup function support? ›

Lookup_value can be a value or a reference to a cell. The range of cells in which the VLOOKUP will search for the lookup_value and the return value. You can use a named range or a table, and you can use names in the argument instead of cell references. The first column in the cell range must contain the lookup_value.

Is Xlookup replacing VLOOKUP? ›

Usage: Both the XLOOKUP and VLOOKUP functions are used to find (lookup) a value from a range and return a related result. The XLOOKUP was introduced in 2019 as a successor to the VLOOKUP. Simply put, XLOOKUP is a more flexible and improved version of the VLOOKUP formula.

Is Xlookup available in all versions of Excel? ›

They are only available in Excel in Microsoft 365 and Office 201 (and in the online version of Excel). You can find custom XLOOKUP-like functions on the internet, but they cannot emulate XLOOKUP 100%, since the latest versions of Excel support dynamic arrays; older versions don't.

What can I replace Xlookup with? ›

XLOOKUP alternatives in Google Sheets
  1. =INDEX(reference, [row], [column])
  2. =MATCH(search_key, range, [search_type])
  3. =INDEX(reference, MATCH(search_key, range, search_type), [column])
  4. =INDEX(C3:C11,MATCH(F3,A3:A11,0))
  5. =Filter(return_array, lookup_array=lookup_value)
  6. =FILTER(C3:C11,A3:A11=F3)
May 9, 2023

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.

How do I activate Xlookup in Excel? ›

To do this, type =XLOOKUP( in the ribbon bar. The next step is to insert your lookup criteria. This is the data you're looking to search for. While XLOOKUP supports numbers and text strings, most users will want to specify a cell reference (such as A2).

What is the limitation of Xlookup? ›

  • 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

Why is Excel lookup function not working? ›

A Column Has Been Inserted

The column index number, or col_index_num, is used by the VLOOKUP function to enter what information to return about a record. Because this is entered as an index number, it is not very durable. If a new column is inserted into the table, it could stop your VLOOKUP from working.

Why is my VLOOKUP not working? ›

Check for any errors in your VLOOKUP formula. If there are errors, it can prevent Excel from updating the values automatically. Make sure that your formula references the correct cells and ranges, and that the data you are looking up is in the correct format. Check your security settings.

Why is xlookup returning 0? ›

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.

Is xlookup available in Excel 2016? ›

The root cause is as you know, Excel 2016 does not support the Xlookup function. It is recommended to try Vlookup or other functions instead.

Is Xlookup available in Excel Online? ›

XLOOKUP in Excel – VLOOKUP, HLOOKUP and LOOKUP in one function. The first thing you should know about the XLOOKUP function is that it is only available in Microsoft Excel Online and Microsoft 365.

What is unsupported features in Office 365 Excel? ›

Unsupported features are contents or functionality in a workbook that prevents the workbook from being opened for editing or, in some cases, viewing in the browser.

What is the alternative to VLOOKUP in Office 365? ›

XLOOKUP was released by Microsoft 365 in 2019 and is meant as the replacement for VLOOKUP, HLOOKUP, INDEX/MATCH functions. Like VLOOKUP or INDEX/MATCH, there are 3 mandatory arguments. The last 3, which are in square brackets, are optional arguments: lookup_value: this is the value you are searching for.

Which charts are not supported in Office 365 Excel Online? ›

Chart types to avoid
Chart typeDifference in a browser
3-D chartsLimited interactive experiences; limited formatting capabilities
Exploded Pie, Pie of Pie, and Bar of Pie chartsLimited interactive experiences; limited formatting capabilities
Stock chartsLimited interactive experiences; limited formatting capabilities
1 more row


Top Articles
Latest Posts
Article information

Author: Rev. Porsche Oberbrunner

Last Updated:

Views: 6011

Rating: 4.2 / 5 (73 voted)

Reviews: 88% of readers found this page helpful

Author information

Name: Rev. Porsche Oberbrunner

Birthday: 1994-06-25

Address: Suite 153 582 Lubowitz Walks, Port Alfredoborough, IN 72879-2838

Phone: +128413562823324

Job: IT Strategist

Hobby: Video gaming, Basketball, Web surfing, Book restoration, Jogging, Shooting, Fishing

Introduction: My name is Rev. Porsche Oberbrunner, I am a zany, graceful, talented, witty, determined, shiny, enchanting person who loves writing and wants to share my knowledge and understanding with you.