XLOOKUP in Excel 2016: Why It Fails and What to Use

Formulas & functions · Updated November 20, 2026

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

Try it: the Formula Explainer: paste any formula and see each function and reference in plain English.

More in Formulas & functions

Sources: Function behaviour and menu paths: Microsoft Support documentation for Excel. Method & sources

Spotted an error in this guide? Let us know.