Fix XLOOKUP Not Recognized in Excel for Mac (M1)
Question details
The user needs to resolve an issue where the XLOOKUP function fails to be recognized in Excel for Mac (version 16.63) on an M1 processor.
- Product
- Microsoft Excel for Mac
- Device & OS
- Mac (Apple M1 Processor)
- Scenario
- Attempting to calculate data using the XLOOKUP function within a spreadsheet.
- Observed behavior
- Excel does not recognize the XLOOKUP function, typically because the workbook is running in Compatibility Mode or is saved in an older legacy file format (.xls).
Verify that your Microsoft 365 subscription is currently active, as XLOOKUP is exclusively available to Microsoft 365 subscribers and users of Excel 2021 or later.
Save the Workbook in a Current Excel Format
Converting the workbook from an older format (such as .xls) to the modern .xlsx format enables advanced features like XLOOKUP to function properly.
When you open a workbook created in an older version of Excel, it often opens in Compatibility Mode. This mode disables newer functions, including XLOOKUP, to prevent compatibility issues. Updating the file format resolves this.
Launch Microsoft Excel for Mac and open the specific spreadsheet where the XLOOKUP function is not being recognized.
Click on 'File' in the top Apple menu bar, then select 'Save As' from the dropdown list.
In the 'File Format' dropdown menu, choose 'Excel Workbook (.xlsx)'. If your file contains macros, choose 'Excel Macro-Enabled Workbook (.xlsm)' instead.
Click 'Save' to create the new version of your workbook. Once saved in the current format, type your XLOOKUP formula again.
Use WPS Office to Seamlessly Handle Modern Excel Functions on Mac
If you are tired of subscription hurdles and file format compatibility issues, WPS Office is a highly compatible, lightweight, and free alternative to Microsoft Office. It natively supports advanced functions like XLOOKUP on both Intel and Apple Silicon (M1/M2) Macs, without requiring you to constantly update legacy file formats.
- 1. Download WPS Office: Download and install the free WPS Office suite for Mac.
- 2. Open Your Spreadsheet: Launch WPS Spreadsheet and open your existing .xls or .xlsx workbook directly.
- 3. Apply XLOOKUP: Enter your XLOOKUP formula. WPS Office recognizes and processes advanced functions seamlessly.
Frequently Asked Questions
Why is XLOOKUP returning a #NAME? error in Excel?
The #NAME? error typically occurs if you misspell the function name, if you are using an older version of Excel (like Excel 2019 or earlier) that does not support XLOOKUP, or if the current workbook is saved in the legacy .xls format.
Is XLOOKUP fully supported on Excel for Mac with Apple Silicon (M1)?
Yes, XLOOKUP is fully supported natively on Apple Silicon (M1/M2) Macs, provided you are using Excel for Mac version 16.63 or later and have an active Microsoft 365 subscription.
Does WPS Office support the XLOOKUP function?
Yes, WPS Spreadsheet fully supports XLOOKUP alongside many other advanced Excel functions, allowing you to perform complex lookups without needing a paid Microsoft 365 subscription.
