How to Get XLOOKUP in Older Versions of Excel: Solutions & Alternatives
Question details
The user wants to use the XLOOKUP function but is working in an older version of Excel that does not support it.
- Product
- Microsoft Excel
- Device & OS
- not provided
- Scenario
- Attempting to write or execute a formula using XLOOKUP for flexible data retrieval in an unsupported desktop version of Excel.
- Observed behavior
- The XLOOKUP function is unavailable in the formula autocomplete list, and manually typing it results in a #NAME? error.
Verify your current Excel version by navigating to File > Account. XLOOKUP is only natively available in Microsoft 365, Excel 2021, and Excel for the web.
Use INDEX and MATCH as an XLOOKUP Alternative
Combine the INDEX and MATCH functions to achieve the exact same flexible, two-way lookup capabilities as XLOOKUP in older Excel versions.
Since Microsoft does not offer a downloadable add-in to bring XLOOKUP to older desktop versions like Excel 2016 or 2019, the standard workaround is using INDEX and MATCH together.
This combination allows you to look up values to the left, specify exact matches, and dynamically handle column additions, completely mimicking XLOOKUP's core functionality.
The standard structure for an INDEX/MATCH formula is: =INDEX(return_range, MATCH(lookup_value, lookup_range, 0)).
Select the cell where you want the result to appear and type =INDEX( to begin.
Highlight the column or data range containing the answer you want the formula to return, then type a comma.
Type MATCH( and click on the cell containing your specific lookup value, followed by another comma.
Highlight the column where the lookup value exists. Type ', 0)' to enforce an exact match, then add a final closing parenthesis ')' to complete the INDEX function and press Enter.
Use Excel for the Web to Access XLOOKUP Natively
If you do not want to upgrade your desktop software, you can use the free online version of Excel which natively includes XLOOKUP.
Access Modern Spreadsheet Functions for Free with WPS Office
If your current version of Microsoft Excel lacks modern functions like XLOOKUP, you don't need to pay for an expensive Microsoft 365 subscription. WPS Office is a powerful, lightweight alternative that supports the latest spreadsheet formulas while maintaining seamless compatibility with your existing Excel files.
- 1. Download and Install: Visit the official WPS website to download and install WPS Office for free on your PC or Mac.
- 2. Open Your Excel File: Launch WPS Spreadsheet and open your existing .xlsx workbook directly.
- 3. Use Advanced Formulas: Start using modern lookup functions seamlessly with full data compatibility.
Frequently Asked Questions
Is there an XLOOKUP add-in for Excel 2016 or 2019?
No, Microsoft does not provide a separate add-in to install XLOOKUP on older desktop versions. You must either upgrade to Excel 2021/Microsoft 365 or use alternative formulas like INDEX and MATCH.
Can I use VLOOKUP instead of XLOOKUP?
Yes, VLOOKUP is available in older Excel versions and can be used for basic searches. However, it requires the lookup value to be in the leftmost column of your range and is prone to breaking if new columns are inserted.
Why does my XLOOKUP formula show a #NAME? error?
The #NAME? error appears because your current version of Excel does not recognize the XLOOKUP function. This usually happens when a workbook created in a newer version of Excel is opened in Excel 2019, 2016, or older.
