XLOOKUP does not work in the standalone desktop version of Excel 2016 because that version does not include the function. Check which Excel edition you have; if it is Excel 2016, replace XLOOKUP with INDEX and MATCH or VLOOKUP, or open the workbook in a version that supports XLOOKUP.
Why XLOOKUP fails in Excel 2016
XLOOKUP was introduced in Microsoft 365 and is available in Excel 2021 and later perpetual releases. It is not included in the perpetual Excel 2016 application, and installing ordinary Excel 2016 updates does not add it. This is a version limitation, not a setting that can be switched on or a missing add-in.
When Excel 2016 encounters an unsupported function, it may show a #NAME? error or display a compatibility marker such as _xlfn.XLOOKUP in the formula. The exact appearance can vary with how the workbook was created and opened. Changing the cell format, recalculating, or saving the file as XLSX will not make Excel 2016 understand XLOOKUP.
There is one common source of confusion: people sometimes call their software Excel 2016 because of its appearance, even though they are using Microsoft 365 Apps, which receives feature updates. Confirm the product rather than relying on the interface or a workbook’s file name.
Check which Excel edition is installed
In Excel for Windows, open File > Account and look under Product Information. The product name and version or build help distinguish a Microsoft 365 subscription from a standalone Office or Excel 2016 license. If you are using Excel on a work or school computer, an administrator may control updates or the installed edition.
You can also ask the person who sent the workbook which version they used. A workbook can contain XLOOKUP even when the computer opening it cannot calculate that formula. The file extension does not establish function support: an XLSX file is a container format, not a guarantee that every Excel version supports every function inside it.
- If Product Information identifies Microsoft 365, install available Office updates and reopen Excel. If the function still appears unavailable, check with your administrator or confirm that you opened the Microsoft 365 installation.
- If it identifies Excel 2016, use a compatible formula or open the file in a supported Excel version.
- If the file is open in another spreadsheet program, check that program’s function compatibility separately; similar-looking formula names are not a guarantee of identical behavior.
What XLOOKUP does
XLOOKUP searches one range for a value and returns a corresponding value from another range. Unlike VLOOKUP, its lookup range does not have to be the leftmost column of a table, and the return range does not have to be specified as a column number. By default, XLOOKUP seeks an exact match, which is useful for IDs, product codes, and names.
For comparison, in a supported version of Excel, with product codes in A2:A10, prices in C2:C10, and the code to find in E2, the formula would be =XLOOKUP(E2,A2:A10,C2:C10). If E2 contains a code on row 5, the formula returns the value from C5. This is a valid example for a supported Excel version, but entering it in standalone Excel 2016 will not make the function available.
XLOOKUP can also specify a value to return when there is no match, search in different directions, and use match modes. Those conveniences do not change the compatibility issue: Excel 2016 cannot calculate XLOOKUP’s options either. When a workbook must be used by people with mixed versions, choose formulas the oldest required version supports.
Replace XLOOKUP with INDEX and MATCH
INDEX and MATCH are a flexible replacement in Excel 2016. MATCH finds the position of the lookup value; INDEX returns the value at that position from the return range. The ranges can be in either left-to-right order, so this approach also works when the return values sit to the left of the lookup column.
For example, assume names are in A2:A5, employee IDs are in B2:B5, and E2 contains the ID to find. Suppose row 2 is Ana with ID 101, row 3 is Ben with ID 104, row 4 is Cara with ID 107, and row 5 is Dev with ID 110. With E2 set to 107, use =INDEX(A2:A5,MATCH(E2,B2:B5,0)). MATCH returns position 3, and INDEX returns the third name, Cara.
The final argument in MATCH, 0, requests an exact match. For lookup tasks such as employee IDs, omitting it can produce unexpected results because MATCH may use an approximate match under its default behavior. Keep the lookup and return ranges the same size and aligned row by row; otherwise, the position MATCH finds may point to the wrong item or create an error.
When VLOOKUP is a suitable alternative
VLOOKUP is another familiar option in Excel 2016, especially when the lookup value is in the first column of a table and the result is to its right. Its limitation is structural: it cannot return a value from a column to the left of the lookup column, and its return column is identified by a number.
For example, assume employee IDs are in B2:B5, names are in C2:C5, and E2 contains the ID to find. If ID 107 is on row 4 and C4 contains Cara, use =VLOOKUP(E2,B2:C5,2,FALSE). The result is Cara. The number 2 tells VLOOKUP to return the second column of the selected table, while FALSE requests an exact match.
Use a table range that includes both the lookup column and the return column. If you insert or rearrange columns, check the column number again. INDEX and MATCH is often easier to maintain when the return column could move or is to the left; VLOOKUP is straightforward when the table layout is stable.
Common problems after replacing the formula
A replacement formula can still fail for reasons unrelated to XLOOKUP’s availability. Check the lookup value, the source data, and the ranges before assuming the formula is wrong. A number stored as text does not always match the same digits stored as a number; extra spaces, inconsistent punctuation, and hidden characters can also prevent a text match.
#N/A: The lookup value may not exist in the selected range, or the two values may differ in type or content. Check for leading or trailing spaces and confirm that an exact match is actually present.#REF!: A referenced cell or range may have been deleted or changed. Edit the formula to point to valid cells.- Wrong VLOOKUP result: Confirm that the lookup column is first in the table range, that the column index identifies the intended return column, and that the final argument is FALSE for an exact match.
- Wrong INDEX/MATCH result: Confirm that MATCH uses the lookup range and INDEX uses a return range with corresponding rows. Use 0 as MATCH’s third argument for an exact match.
- Formula appears as text: If the cell displays the formula instead of calculating it, check whether the cell is formatted as Text. Change it to General, then re-enter the formula. Also check whether Show Formulas is enabled.
Excel’s argument separator depends on regional settings. The examples here use commas; some installations expect semicolons instead. If Excel flags a comma-separated formula as invalid, use the separator configured for your installation. That separator difference does not add XLOOKUP support to Excel 2016.
Sharing a workbook across Excel versions
If a workbook containing XLOOKUP is shared with someone using Excel 2016, their copy may not calculate the formula correctly. Ask collaborators to open the workbook in Microsoft 365 or Excel 2021 and later, or replace XLOOKUP with a formula supported by all intended users. Simply saving the workbook in XLSX format does not translate the function into an older one.
Before distributing a workbook, test a copy using the oldest Excel version the recipients need to support. Check not only whether the formula displays an answer, but also whether it updates when the source data changes. If recipients must only view a fixed result, a pasted value may be an option, but it will not update when the underlying data changes. Keep a formula-driven master copy if future recalculation matters.
Choosing the right fix
Choose based on the workbook’s users and layout, not just on which formula is shortest. Upgrading or opening the workbook in a supported version is the direct solution when XLOOKUP itself is required. If Excel 2016 is a firm requirement, INDEX and MATCH is a strong general-purpose replacement; VLOOKUP is reasonable for a stable table with the lookup column on the left.
- Use XLOOKUP when everyone who needs to calculate the workbook has a supported Excel version and its extra search options are useful.
- Use INDEX and MATCH when the formula must work in Excel 2016 and the return column may be on either side of the lookup column.
- Use VLOOKUP when the lookup field is the first column of a stable table and a simple exact-match lookup is enough.
There is no Excel 2016 preference, compatibility pack, or ordinary formula setting that enables the XLOOKUP function. If a proposed fix tells you only to update Excel 2016, verify the product edition: that advice may apply to updating Microsoft 365, not to adding a later function to the standalone 2016 release.
Frequently Asked Questions
Can I install XLOOKUP in Excel 2016?
No. XLOOKUP is not part of standalone Excel 2016, and it cannot be installed as an add-in to that version. Use a compatible formula or use Excel that supports the function.
Why does Excel show _xlfn.XLOOKUP?
The marker indicates that the Excel version opening the workbook does not recognize the function name. In Excel 2016, replace the formula with INDEX and MATCH or VLOOKUP, or open the file in a supported version.
Does XLOOKUP work in Excel 2019?
No. The perpetual Excel 2019 release does not include XLOOKUP. Microsoft 365 and Excel 2021 and later releases support it.
Will saving the workbook as XLSX fix the error?
No. XLSX is a file format and does not convert XLOOKUP into a function Excel 2016 can calculate. The workbook must be opened in a supported version or use a compatible formula.
What is the closest alternative to XLOOKUP in Excel 2016?
INDEX and MATCH is usually the most flexible alternative because it can return values from either side of the lookup range. VLOOKUP is also suitable when the lookup column is first in the table and the return column is to its right.
Related
- Undo vs Redo Excel: What Each Command Does
- Ungroup Excel Shortcut: Remove Row and Column Groups
- Excel Custom Number Formats: How to Create and Use Them
Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.
More in Formulas & functions
- Excel Formula Codes: A Practical Function ListExcel formula codes usually means formulas and the function names inside them: each formula begins with an equals…
- Formula to Capitalize Each Word in ExcelIn Excel, use the PROPER function to capitalize the first letter of each word: with the text to…
- Excel LET: Can It Use Multiple Variables?Yes. Excel’s LET function allows multiple named values in one formula: write each name and value as a…
- Excel Formula to Find and Replace Text in Cells: ExamplesTo replace text inside a cell with an Excel formula, use SUBSTITUTE. If the original text is in…
- Show Errors as Blank in Excel: Formula and OptionsYes. In Excel for Microsoft 365, wrap a formula in IFERROR and return an empty text string when…
- Excel Filter Between Text Ranges: Steps and LimitsYes. In Excel for Microsoft 365 on Windows, open the column’s filter menu, choose Text Filters > Custom…
Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources
Spotted an error in this guide? Let us know.