XLOOKUP Not Working in Excel 2019? Fixes and Options

XLOOKUP is not included in Excel 2019, so updating that version or changing the formula will not make the function available. To get the result, use a compatible alternative such as INDEX and MATCH, or open the workbook in Microsoft 365 or Excel 2021 or later.

Why XLOOKUP is missing from Excel 2019

Excel 2019 is a one-time-purchase, perpetual version of Excel. Its feature set is different from Microsoft 365, which receives new functions through updates. XLOOKUP was not included in Excel 2019; it is available in Microsoft 365 and Excel 2021 and later, including Excel 2024. That difference is about the product version, not a setting that can be switched on.

Installing the latest updates for Office 2019 can fix bugs and security issues, but it does not add XLOOKUP to that edition. Likewise, repairing Office, reinstalling it, changing regional settings, or copying the formula from another workbook will not add the missing function. If another person sent you a file using XLOOKUP, Excel 2019 may show an unsupported-function error, such as #NAME?, or display an _xlfn prefix with the function. The workbook may still open, but formulas relying on XLOOKUP may not calculate correctly in that version.

First confirm which Excel product is installed. In Windows, go to File > Account > About Excel. On a Mac, use Excel > About Microsoft Excel. Check whether the product is labeled Excel 2019, Microsoft 365, or a later perpetual release. A computer can have a Microsoft 365 account and still be running an older Office installation, so the product name matters.

What XLOOKUP does in supported versions

XLOOKUP searches one range or array for a value and returns a corresponding value from another range or array. Unlike VLOOKUP, it does not require the search column to be the leftmost column in a table, and it does not use a numeric column index. It can also return a custom message when there is no match and can search from the last item toward the first.

For example, with product IDs in A2:A6, product names in B2:B6, and the ID to find in E2, this supported-version formula returns the matching name or a message if the ID is absent: =XLOOKUP(E2,A2:A6,B2:B6,"Not found",0). If E2 contains 103 and the corresponding name in B is Cable, the result is Cable. The final 0 requests an exact match; exact matching is also XLOOKUP’s default.

Knowing how the function works helps distinguish a version problem from a data problem. In a supported Excel release, a misspelled function name, mismatched range sizes, or a lookup value stored as text when the source is numeric can still cause errors or unexpected results. But in Excel 2019, the underlying issue is simpler: the function is not part of the application.

Use INDEX and MATCH as an Excel 2019 replacement

INDEX and MATCH provide a flexible replacement and work in Excel 2019. MATCH finds the position of a value in a range; INDEX returns the item at that position in another range. With product IDs in A2:A6, product names in B2:B6, and the requested ID in E2, enter: =IFERROR(INDEX($B$2:$B$6,MATCH(E2,$A$2:$A$6,0)),"Not found"). If E2 is 103 and that ID is on the third row of the lookup range, the formula returns the third corresponding name, Cable. If there is no match, IFERROR displays Not found.

The zero in MATCH requests an exact match. The two ranges should cover corresponding rows and have the same number of cells. Dollar signs lock the ranges when you copy the formula, while E2 remains relative and changes to E3, E4, and so on. This is useful when filling the formula down for a list of IDs.

INDEX and MATCH can look to the left as well as the right. For instance, if product names are in A2:A6 and product IDs are in B2:B6, the same pattern can search the ID column and return the name column by placing B2:B6 inside MATCH and A2:A6 inside INDEX. This avoids rearranging a source table just to accommodate a lookup.

When VLOOKUP is enough

VLOOKUP is another built-in option in Excel 2019 and may be easier for a simple table where the lookup column is on the left. With IDs in A2:A6, names in B2:B6, and the requested ID in E2, use: =IFERROR(VLOOKUP(E2,$A$2:$B$6,2,FALSE),"Not found"). If E2 is 103 and that row’s name is Cable, it returns Cable. FALSE requests an exact match; IFERROR supplies a readable result when the ID is not found.

VLOOKUP’s 2 tells Excel to return the second column in the selected table range. That makes it sensitive to column changes: inserting or rearranging columns can make the index point somewhere different. It also cannot return a value from a column to the left of the lookup column without changing the layout or using another method. INDEX and MATCH is usually the better fit when those limitations matter; VLOOKUP remains a straightforward choice for a stable, simple table.

Check whether the issue is really the version

If you believe you have a supported Excel version, check the product name and update status before rewriting the workbook. In Microsoft 365 for Windows, use File > Account > Update Options > Update Now, if that option is available. Managed work or school devices may have updates controlled by an administrator. On Mac, check for updates through Help > Check for Updates or the Microsoft AutoUpdate application, depending on how Office was installed.

Then test a small formula in a blank cell. With a lookup value in E2, keys in A2:A3, and return values in B2:B3, this formula should return the matching value in a version that supports XLOOKUP: =XLOOKUP(E2,A2:A3,B2:B3,"Not found"). If the same function produces an unsupported-function error, verify that the Excel app actually running is the supported product. A formula entered in Excel for the web, for example, can behave differently from the same file opened later in an old desktop edition.

Also check the workbook’s source data if Excel recognizes the function but returns an unexpected result. Common causes include leading or trailing spaces, numbers stored as text, duplicate keys, and looking in the wrong range. If IDs are meant to be unique but appear more than once, a lookup returns one matching record rather than warning that the key is duplicated. Clean or validate the source values when accuracy depends on uniqueness.

Choosing how to open a workbook that uses XLOOKUP

If you need to preserve XLOOKUP formulas rather than replace them, open the file in Microsoft 365 or Excel 2021 or later. Excel for the web also supports XLOOKUP, so it can be a practical way to view or edit a workbook when the account and file are available there. Check the result in the supported app before relying on it; an unsupported desktop version may not calculate the formula.

If the workbook must be shared with people who use Excel 2019, consider replacing XLOOKUP with INDEX and MATCH, or with VLOOKUP where its limitations are acceptable. Ask recipients which versions they use before distributing a file that depends on newer functions. Saving as .xlsx does not convert a newer function into an older one: the file format can hold the workbook, but the older Excel application still needs to understand each formula.

There is a trade-off. Keeping XLOOKUP can make formulas easier to read and maintain for users on current versions, while compatibility formulas can be more broadly usable. If you replace formulas, test the workbook’s important cases: a normal match, a missing value, a duplicate key if possible, and any formulas copied across or down. Also check dependent totals, charts, and reports, since a lookup change can affect more than the visible result cell.

Common mistakes when replacing XLOOKUP

Using approximate matching by accident: In VLOOKUP, the fourth argument should be FALSE for an exact match. In MATCH, use 0. Leaving out the exact-match setting in VLOOKUP can produce a misleading result when the first column is not sorted as required for approximate matching.
Using ranges of different lengths: The lookup range and return range in INDEX and MATCH should line up row for row. A mismatch can return the wrong record or create a formula problem.
Forgetting to handle a missing key: IFERROR can show a useful message instead of an error, but do not use it to hide every possible problem without investigating. A miss can indicate a typo or a data-quality issue, not just an ordinary absent record.
Assuming the first match is the only match: Traditional lookup formulas return a single result. If duplicate IDs exist, decide whether the first occurrence is appropriate or whether the source data needs a separate duplicate check.

Frequently Asked Questions

Can I install XLOOKUP in Excel 2019?
No. XLOOKUP is not included in Excel 2019, and an Office update or add-in cannot install it as a built-in worksheet function. Use INDEX and MATCH or VLOOKUP, or use a supported Excel version.

Does Excel 2019 support XLOOKUP after an update?
No. Updates maintain the Office 2019 release but do not add this newer function. Confirm the product listed under About Excel rather than relying only on whether Office says it is up to date.

What is the closest XLOOKUP alternative in Excel 2019?
INDEX and MATCH is a flexible alternative for exact lookups, including lookups that return a value from a column to the left. VLOOKUP is also suitable when the lookup column is at the left of a stable table.

Will a workbook with XLOOKUP work in Excel 2019?
The workbook may open, but Excel 2019 cannot calculate XLOOKUP as a supported function. Cells using it may show an error or an unsupported-function marker. Open the file in Microsoft 365 or Excel 2021 or later, or replace the formulas for compatibility.

Is XLOOKUP available in Excel 2021?
Yes. XLOOKUP is available in Excel 2021 and later, as well as Microsoft 365. That is different from Excel 2019, even if both products are installed on the same computer or appear in older help pages.

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.