XLOOKUP was introduced in Excel 365 (in September 2019) and allows you to perform advanced lookups in Excel. However, it is not backward compatible with older versions of Excel.
If you are using Excel 365 and you have created a workbook with XLOOKUP formulas, you need to be cautious when sharing that workbook with users who have older versions of Excel (such as Excel 2019, 2016, or earlier). XLOOKUP functions will not work in those versions, and the users will encounter errors when they open the file.
To ensure backward compatibility and avoid errors, you have a few options:
- Use IFERROR: Wrap your XLOOKUP formulas with the IFERROR function and provide an alternative lookup method for older versions of Excel. For example, you can use VLOOKUP or INDEX/MATCH. This way, the formula will fall back to a compatible lookup method if XLOOKUP is not available.excelCopy code
=IFERROR(XLOOKUP(...), VLOOKUP(...))Remember to adjust the formula to use a lookup method that is compatible with the older versions of Excel. - Convert to alternative formulas: Replace your XLOOKUP formulas with alternative formulas that work in older versions of Excel. For example, you can convert an XLOOKUP formula to an INDEX/MATCH formula. This might require modifying your existing formulas and understanding the syntax differences between XLOOKUP and the alternative functions.
- Inform users about compatibility: If you are sharing the workbook with users who have older versions of Excel, inform them about the requirement of Excel 365 and the use of XLOOKUP. This way, they can decide to upgrade their Excel version or use alternative methods to achieve the same results.
Remember to test your workbook thoroughly in older versions of Excel to ensure that the fallback methods or alternative formulas work as expected.