XLOOKUP is available in Microsoft 365, Excel 2021, and Excel 2024, as well as Excel for the web and supported mobile apps. It is not included in Excel 2019 or earlier, so if you use one of those versions you will need an alternative such as VLOOKUP or INDEX with MATCH.
Which Excel versions support XLOOKUP?
For desktop Excel, the key dividing line is the release: XLOOKUP is included in Excel 2021 and later, but not in Excel 2019, Excel 2016, or older perpetual releases. It is also available in the Microsoft 365 desktop apps. Microsoft 365 is updated over time, so if you have a current installation but cannot use the function, check that Office is up to date and confirm which Excel edition is installed.
XLOOKUP also works in Excel for the web and in supported Excel apps for phones and tablets. The same workbook can therefore use XLOOKUP across current desktop and online versions. A person opening that workbook in an older desktop release, however, may not be able to calculate the formula.
- Supported: Microsoft 365, Excel 2021, Excel 2024, Excel for the web, and supported mobile apps.
- Not supported: Excel 2019, Excel 2016, and earlier desktop versions.
- Mac: The same release distinction applies. XLOOKUP is supported in Microsoft 365 for Mac and Excel 2021 or later for Mac, not older perpetual editions such as Excel 2019 for Mac.
Why XLOOKUP was introduced
XLOOKUP is a newer lookup function designed to make finding information in tables more flexible than older formulas such as VLOOKUP. A common VLOOKUP limitation is that the lookup column must be the first column in the selected table, and the formula uses a column number to identify where the answer should come from. Inserting or rearranging columns can make that setup awkward.
XLOOKUP uses separate lookup and return ranges. That means it can search one column and return a value from a column to its left or right, without counting columns in a table. It also uses exact matching by default and has an optional argument for a custom result when no match is found. These features make it useful for product lists, employee records, price tables, and other structured data.
How to check your Excel version
On Windows, open Excel and choose File > Account > About Excel. The About window identifies the installed product and version. If the product is Microsoft 365, its update channel and build information can help explain why a feature may not yet appear on a particular installation.
On a Mac, open Excel and choose Excel > About Microsoft Excel from the menu bar. In Excel for the web, the app runs in a browser and is updated by Microsoft, rather than being identified by a traditional desktop release number. Menus can vary slightly with app updates, but the product name and version information are the details to look for.
If you have Microsoft 365 and XLOOKUP is unavailable, install available Office updates and reopen Excel. On Windows, the update controls are generally under File > Account > Update Options. If the computer is managed by an employer or school, an administrator may control when updates are delivered. Check the exact product name before assuming that an older workbook or an Excel-like application supports every Microsoft 365 function.
How XLOOKUP works
At its simplest, XLOOKUP searches a range for a value and returns the corresponding value from another range. The lookup and return ranges should line up: if the search range has four rows, the return range normally has four corresponding rows. Unlike VLOOKUP, the return range does not need to be to the right of the search range.
By default, XLOOKUP looks for an exact match and searches from the first item to the last. Its optional arguments can change the missing-match message, the kind of match, or the search direction. For example, match settings can request the next smaller or next larger value, or use wildcard matching. Search settings can search from the bottom upward. Binary search modes are also available, but require the lookup data to be sorted in the appropriate order; using them on unsorted data can give incorrect results.
XLOOKUP can return more than one column when the return range contains multiple columns. In current Excel versions, the returned values can spill into neighboring cells automatically. Make sure those cells are empty; otherwise Excel may show a spill error. A single-result lookup does not require the surrounding cells to be clear.
A basic example with a product list
Suppose A2:A5 contains product names and B2:B5 contains their prices: Pen, Notebook, Folder, and Ruler, with prices 1.25, 2.50, 3.00, and 0.90 respectively. Cell D2 contains the product name to find. With that layout, this formula returns the matching price:
=XLOOKUP(D2,A2:A5,B2:B5,"Not found")
If D2 contains Notebook, the result is 2.50. If it contains a name not listed in A2:A5, the result is the text “Not found” instead of the usual missing-match error. Leaving out the fourth argument is also valid, but an unmatched lookup then returns an error value. Using a clear message can make a report easier to understand, although it can also hide the reason a value is missing if the message is too generic.
To search for an employee’s department, the same pattern applies: search the employee ID range, then return the corresponding department range. Keep the ranges the same size and ensure that each row in the return range belongs to the same record as the row beside it in the lookup range. A mismatch in the starting or ending rows can return a plausible-looking but incorrect answer.
Finding the last matching record
XLOOKUP normally returns the first match it encounters. Its search-direction option can instead start at the bottom of a range, which is useful when repeated IDs appear and the most recently entered record is stored last. This finds the last matching entry in the listed data; it does not determine which date is latest unless the rows are arranged accordingly.
Assume employee IDs are in A2:A100, notes are in C2:C100, and E2 contains the ID to find. This formula searches from the final row upward and returns the corresponding note:
=XLOOKUP(E2,A2:A100,C2:C100,"No match",0,-1)
If the ID in E2 appears more than once, the result comes from its last occurrence in A2:A100. The zero requests an exact match, while -1 sets the search direction to last-to-first. If the data is not ordered by date, use a date-based approach to identify the newest record rather than assuming the bottom row is the newest.
XLOOKUP compared with VLOOKUP and INDEX/MATCH
For a new workbook used only in current Excel releases, XLOOKUP is often the simpler choice for a straightforward lookup. It can search in either direction, defaults to exact matching, and has a built-in missing-result option. VLOOKUP remains useful when a workbook must run in older Excel versions or when existing formulas already use it and work reliably.
INDEX with MATCH is another established alternative. It can look left or right and works in older releases that do not have XLOOKUP, although it usually takes more effort to read and maintain. Compatibility matters more than choosing the newest function: a workbook shared with Excel 2019 users should not depend on XLOOKUP unless those users will open it in a supported version.
For large workbooks, formula design and data quality also matter. Avoid mismatched ranges, confirm that lookup values use consistent types, and check whether IDs that look alike are stored as text in one place and numbers in another. XLOOKUP cannot resolve an underlying data inconsistency simply because it has more flexible syntax.
Sharing workbooks with older Excel versions
A workbook containing XLOOKUP can be saved in the standard .xlsx format, but that does not add the function to older Excel installations. When an unsupported version opens the file, it may show an #NAME? error or display an _xlfn. prefix in connection with the unsupported function. The workbook may still open, but formulas relying on XLOOKUP will not calculate correctly there.
Before sharing a file, ask recipients which Excel versions they use. If they have Excel 2019 or earlier, replace XLOOKUP with a compatible formula, provide a version of the results as values when suitable, or ask them to use Excel for the web or a supported desktop release. Converting formulas to fixed values removes their automatic updating, so it is appropriate only when recipients do not need to recalculate the lookup.
Common reasons XLOOKUP does not work
An unavailable-function error usually points to an unsupported Excel release, but other issues can cause unexpected results even in a supported version. Check the function spelling, the selected ranges, and the actual contents of the lookup cells. A cell can appear to contain the same ID as another cell while differing in spaces, punctuation, or data type.
- #NAME? or _xlfn: Check whether the workbook is open in Excel 2019 or earlier, or in an application that does not support XLOOKUP.
- Not found: Confirm that the lookup value exactly matches the source, including spaces and text-versus-number differences. Check that the lookup range includes the expected rows.
- Wrong record: Look for duplicate values. The default search returns the first match, so choose a last-to-first search only if the last matching row is the intended record.
- Spill error: If the formula returns multiple cells, clear any occupied cells in the area where the results need to appear.
- Unexpected approximate result: Review the match-mode and search-mode arguments. In particular, binary search requires correctly sorted data.
Frequently Asked Questions
Is XLOOKUP available in Excel 2019?
No. Excel 2019 does not include XLOOKUP. Use a compatible alternative such as VLOOKUP or INDEX with MATCH, or open the workbook in Microsoft 365, Excel 2021 or later, or Excel for the web.
Does XLOOKUP work in Excel for Mac?
Yes, if you use Microsoft 365 for Mac or Excel 2021 or later. Older Mac editions, including Excel 2019 for Mac, do not support the function.
Is XLOOKUP available in Excel for the web?
Yes. Excel for the web supports XLOOKUP, so a workbook using it can be used in a browser. Someone opening the same file in an unsupported desktop version may still see an error.
Why does Excel show #NAME? for XLOOKUP?
The most common reason is that the installed Excel version does not recognize the function, especially Excel 2019 or an earlier release. Verify the product and version, then update Microsoft 365 if applicable. A misspelled function name can also produce an error.
Is XLOOKUP better than VLOOKUP?
It is more flexible for many current Excel tasks: it can return values from either side of the lookup range, uses exact matching by default, and can display a chosen result when there is no match. VLOOKUP may be preferable when compatibility with older Excel releases is essential.
Related
- Python pandas Excel Merge: Join Workbooks and Sheets
- Python pandas Excel Formatting: A Practical Guide to Styles
- Correlation Table in Excel: Create and Read One
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- EDATE in Excel: How to Add Years to a Date CorrectlyExcel’s EDATE function adds months, not years. To add a whole number of years to a date in…
- Round Excel Numbers Without a Formula: Display or ChangeYes. In Excel for Microsoft 365 on Windows, use a number format to display fewer decimal places without…
- Excel Column Width to Match Row Height?Excel has no built-in setting that makes a column’s width equal a row’s height: column width is measured…
- XLOOKUP Example Table: Formulas, Results, and Tips in ExcelWith product IDs in A2:A6, prices in C2:C6, and the ID to find in E2, use =XLOOKUP(E2,A2:A6,C2:C6,"Not found").…
- Excel MID Formula: Extract Text by PositionUse Excel’s MID function to extract a specified number of characters from a text value, starting at a…
- Excel Compare Two Sheets: Methods and FormulasTo compare matching cells on two sheets in the same workbook, enter ='Sheet1'!A1='Sheet2'!A1 in a blank cell; Excel…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.