XLOOKUP Not Working in Excel 2016? The Real Reason

No—Excel 2016 does not include XLOOKUP, so the function will not work there even if the formula is typed correctly. Use VLOOKUP or INDEX/MATCH instead, or open the workbook in Microsoft 365 or Excel 2021 or later if you need XLOOKUP itself.

Why XLOOKUP Fails in Excel 2016

XLOOKUP is a newer lookup function, not a feature that can be added to Excel 2016 by installing a routine update. It is available in Microsoft 365 and Excel 2021 or later; the perpetual-license version of Excel 2016 does not support it. That means changing commas, adding dollar signs, or adjusting the lookup range will not make an XLOOKUP formula calculate in a genuine Excel 2016 installation.

The most common clue is a #NAME? error. Excel shows that error when it does not recognize a function name, among other possible causes. If a workbook containing XLOOKUP was created in a newer Excel version, Excel 2016 may instead show the function with an _xlfn. prefix, or show an error in the result cell. The precise display can depend on how the file was created and opened, but the underlying issue is the same: this version cannot calculate XLOOKUP.

There is an important naming wrinkle. People sometimes call any desktop Excel installation “Excel 2016” because that is the version they first installed. A Microsoft 365 subscription, however, receives the Microsoft 365 edition of Excel and can receive newer functions; it is not the same product as the standalone Excel 2016 license. Confirm the installed product before concluding that the workbook is running in Excel 2016.

Confirm Which Excel Version You Have

On Windows, open Excel and select File > Account. The product information appears under Product Information, and About Excel shows more detailed version and build information. If it identifies Office/Excel 2016 as a perpetual product, XLOOKUP is unavailable. If it identifies Microsoft 365, check that Excel is updated and that you are opening the file in that Microsoft 365 installation rather than an older copy.

On a Mac, open Excel and use Excel > About Microsoft Excel to check the application version and license context. The names and account details can differ somewhat by installation. Excel for the web is another option for a Microsoft 365 account, but the key point is to open the workbook in a version that supports the function. Merely changing the file extension or copying the formula into Excel 2016 cannot add function support.

If another person supplied the workbook, ask which Excel version they used and whether the formula is intended to be XLOOKUP. A formula copied from a web page may also contain a typo or use a different list separator because of regional settings, but those issues are separate from Excel 2016’s lack of support.

What Happens When You Share an XLOOKUP Workbook

Excel 2021 and Microsoft 365 can calculate XLOOKUP, but an older Excel installation may not. If you save a workbook with XLOOKUP and send it to someone using Excel 2016, that person may see an error rather than the calculated answer. Do not assume that a formula which works on your computer will work for every recipient: the recipient’s Excel version matters.

For a workbook that must be usable in Excel 2016, replace XLOOKUP formulas with compatible formulas before sharing, or provide a separate values-only copy when formulas are not needed. Converting results to values removes the formulas and therefore stops them updating when source data changes. Keep a formula-enabled master copy if future updates are important.

For a team that can standardize on Microsoft 365 or Excel 2021 and later, XLOOKUP can be a sensible shared approach. If even one essential user must work in Excel 2016, choose a compatible formula or verify the workbook with that user’s software before relying on it.

Use VLOOKUP as a Direct Replacement

VLOOKUP is often the quickest substitute when the value to find is in the leftmost column of a table and the result is in a column to its right. It supports exact matching, which is usually what a person expects from a basic XLOOKUP. In the example below, the lookup value is P-104; the table has product codes in column A and descriptions in column B.

With product codes in A2:A4, descriptions in B2:B4, and the code P-104 in D2, enter =VLOOKUP(D2,A2:B4,2,FALSE) in E2. With rows P-101 / Bolts, P-104 / Washer, and P-109 / Tape, the formula returns Washer. The final argument, FALSE, requests an exact match; leaving it out can produce an approximate-match result, which is risky when the list is not sorted as required.

VLOOKUP has a structural limitation: it searches only the first column of its table range and returns a value from a column to the right. If codes are in column B but the desired result is in column A, VLOOKUP cannot return leftward from a range beginning with column B. Its column-number argument also needs attention if columns are inserted into the lookup table. For a simple, fixed, left-to-right table, though, it is familiar and widely compatible.

Use INDEX and MATCH for More Flexible Lookups

INDEX/MATCH is a useful alternative when the return column is to the left of the lookup column, or when you want to avoid counting a return-column number. MATCH finds the position of the sought value; INDEX returns the item at that position from the chosen result range. The ranges must cover corresponding rows.

With product codes in A2:A4, descriptions in B2:B4, and P-104 in D2, enter =INDEX(B2:B4,MATCH(D2,A2:A4,0)) in E2. For the sample rows P-101 / Bolts, P-104 / Washer, and P-109 / Tape, it returns Washer. The zero tells MATCH to look for an exact match. Unlike VLOOKUP, the lookup and return ranges can be in either column order.

If the code does not exist in the lookup list, this formula returns #N/A, which is useful when a missing match should be investigated. If a cleaner display is preferable, wrap the calculation in IFERROR. With the same ranges and input cell, =IFERROR(INDEX(B2:B4,MATCH(D2,A2:A4,0)),"Not found") displays Not found when there is no match. Because this hides errors from the lookup as well as missing values, use it only when masking other errors will not conceal a problem.

Choose the Best Alternative for Your Workbook

The right substitute depends on how the data is arranged and who needs to open the file. For a small table with a lookup column at the left, VLOOKUP is straightforward. For lookups that need to return values from either side, INDEX/MATCH is more flexible. In either case, use exact matching for identifiers such as product codes, employee IDs, or invoice numbers.

Choose VLOOKUP when the search column is first in the selected range and the result column is to its right.
Choose INDEX/MATCH when the result may be to the left, or when you want separate lookup and result ranges.
Choose XLOOKUP only when all intended users have Excel 2021 or Microsoft 365 (or another supported Excel environment) and the workbook will not need to calculate in Excel 2016.

Before replacing many formulas, test the alternative on a few known records: a match near the top, one near the bottom, and a value that is absent. Check that returned results are correct and that the no-match behavior is appropriate. This small check can reveal mismatched ranges, duplicate keys, or a wrong match setting before the formulas spread through a report.

Common Problems That Look Like Version Issues

If your Excel installation is a supported version but XLOOKUP still fails, check the exact error and formula. A misspelled function name or missing opening parenthesis can cause a name or formula error. A valid formula can return #N/A when the lookup value is absent, or when values that look identical differ because one is stored as text and the other as a number. Extra spaces in imported text can also prevent an exact match.

Inspect the lookup and return ranges to ensure they start and end on corresponding rows. In XLOOKUP, the lookup array and return array need compatible dimensions; selecting a whole column for one and a differently sized segment for the other can cause problems. Also check whether the formula points to the intended sheet and whether a referenced workbook is available.

Some regions use semicolons rather than commas as formula separators. That can make a copied formula appear invalid even in an Excel version that supports the function. Follow the separator Excel expects in your regional settings. But if the application is confirmed to be standalone Excel 2016, separator changes will not solve the missing-function problem.

Frequently Asked Questions

Can I install XLOOKUP into Excel 2016?
No. It is not an add-in or a separate function pack for Excel 2016. To calculate XLOOKUP, use a supported Excel version such as Microsoft 365 or Excel 2021 or later; otherwise replace it with a compatible formula.

Why does Excel 2016 show #NAME? for XLOOKUP?
Excel 2016 does not recognize XLOOKUP as a built-in function, so it can return #NAME?. First verify the application and license under File > Account on Windows. If it really is Excel 2016, use VLOOKUP or INDEX/MATCH.

Will an XLOOKUP formula work for someone using Excel 2016?
Not as a calculated XLOOKUP formula. The recipient needs a version that supports the function, or the workbook author must replace it with an older compatible formula or share fixed values. Test the actual workbook on the recipient’s version if compatibility is essential.

Is INDEX/MATCH better than VLOOKUP?
Neither is best for every case. INDEX/MATCH can return values from either side of the lookup column and avoids VLOOKUP’s column index number. VLOOKUP is simpler for a conventional table with the lookup field at the left and the result to its right.

Does Excel 2019 support XLOOKUP?
The perpetual-license edition of Excel 2019 does not support XLOOKUP. Microsoft 365 is a different, subscription-updated product, so check the installed product name rather than relying only on the age of the computer or the look of the interface.

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

More in Errors & fixes

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

Spotted an error in this guide? Let us know.