Function Problems

How to Detect XMATCH and XLOOKUP Support in Excel

Steven ParkerSteven Parker Nov 3, 2026 869 views

Question details

The user needs a reliable method to determine if the XMATCH and XLOOKUP functions are available in a specific Excel installation.

How to Detect XMATCH and XLOOKUP Support in Excel
Product
Microsoft Excel
Device & OS
Windows and macOS
Scenario
Developing a spreadsheet or macro that relies on newer Excel functions but needs to ensure compatibility across different user environments.
Observed behavior
Checking the Excel release number using INFO("RELEASE") fails to indicate feature support because availability depends on update channels, subscription models, and licenses rather than just the base version number.
Before you start

Before testing for function compatibility, ensure that your workbook is not locked or protected, as you may need to insert a temporary hidden worksheet to safely perform the detection.

Solution 1Recommended

Programmatically Test the Function in a Hidden Worksheet

The most foolproof way to check for XLOOKUP or XMATCH support is to use a macro to write a test formula and evaluate the result.

Because Microsoft rolls out features gradually based on subscription types and update channels, versions with the same release number (like 16.0) might have different capabilities. Testing the function directly bypasses this confusion and ensures you capture the exact capabilities of the user's specific build.

1
Create a test worksheet

Use VBA or manually create a new, temporary hidden worksheet in your Excel workbook to serve as a safe testing environment.

2
Enter a safe test formula

Input a known XLOOKUP or XMATCH formula into a specific cell (for example, =XLOOKUP(1,{1},{1})).

3
Evaluate the result

Use your macro script to read the calculated value of that cell.

4
Check for errors

If the cell returns the expected numerical or text result, the function is supported. If it returns an unsupported function error (like #NAME?), it is not.

5
Clean up

Delete or clear the hidden worksheet after the compatibility check is complete to keep your workbook tidy.

Programmatically Test the Function in a Hidden Worksheet
Best Practice: This direct testing method works consistently across both Windows and Mac versions of Excel, regardless of the user's specific license type or update channel.
Use Advanced Functions in WPS Office

Use XLOOKUP and XMATCH Seamlessly with WPS Spreadsheet

WPS Spreadsheet provides robust support for modern functions like XLOOKUP and XMATCH right out of the box. You can perform highly efficient data analysis and lookup tasks without worrying about complicated version rollouts or license types.

  1. 1. Open your workbook: Launch WPS Spreadsheet and open the document where you need to perform data lookups.
  2. 2. Initiate the function: Select an empty cell and type =XLOOKUP( to start building your formula.
  3. 3. Input arguments: Select your lookup_value, highlight the lookup_array, and choose the return_array according to the on-screen prompts.
  4. 4. Execute the formula: Press Enter to instantly retrieve your accurately matched data.
Full compatibility with Microsoft Excel formulas including XLOOKUP and XMATCHFree and lightweight alternative for powerful data analysisCross-platform support for Windows, macOS, Linux, iOS, and AndroidFamiliar user interface requiring zero learning curve
QA img-9

Frequently Asked Questions

Why does INFO("RELEASE") return 16.0 but I do not have XLOOKUP?

Microsoft Excel uses version 16.0 as a baseline for multiple different builds. XLOOKUP was released via specific update channels and Microsoft 365 subscriptions. This means two users with release 16.0 might have entirely different feature sets based on their respective licenses.

What does the #NAME? error mean when using XLOOKUP?

The #NAME? error typically indicates that the function you are trying to use is not recognized by your version of Excel. If you see this when typing an XLOOKUP or XMATCH formula, it confirms that the function is unsupported in your current installation.

Can I use IFERROR to handle unsupported XLOOKUP functions?

Yes, you can wrap your formula in IFERROR, but if the underlying function is completely unrecognized (#NAME?), it is better practice to use a combination of INDEX and MATCH as a fallback formula to ensure backward compatibility for users on older Excel versions.

Are XLOOKUP and XMATCH available in Excel 2016 or 2019?

No, XLOOKUP and XMATCH are generally not available in the standalone, non-subscription versions of Excel 2016 and 2019. They are primarily supported in Microsoft 365 subscriptions and Excel 2021 or newer.