XLFN

The XLFN prefix in Excel formulas indicates that the current Microsoft Excel version does not support the given function.

If you open a workbook that you created an earlier Excel version, some formulas may show an _xlfn prefix at the starting position of the function. The prefix is an alert, and Excel will indicate that the expression contains an unsupported function.

XLFN prefix in Excel

In the example, we use our favorite lookup function, XLOOKUP. Good to know that the function is only available in Microsoft 365 for Excel (subscription).

First, let us open the Workbook in Excel 2016. At first glance, the calculation result looks correct, but at the beginning of the formula, the _xlfn.XLOOKUP appears.

_xlfn xlookup excel 2016

Try to update the source table; the function will return a #NAME? Error value because Excel cannot update the function and recalculate the result.

unsupported function excel

Note: Open the file again in the latest Excel. In this case, Excel has built-in native XLOOKUP support; the _xlfn prefix will not appear. You can modify the formula or the source range, and the calculation will be right.

How to fix incompatible functions

The most effective method is to remove the unsupported _xlfn function (or the incompatible part of the formula) from the formula and replace it with a supported function.

In this case, the point is that we want to find a left lookup replacement. Alternatively, you can use the VLOOKUP and CHOOSE combination to perform a left lookup. The demonstrated formula below works fine with Excel 2013, Excel 2016, and Excel 2019 too.

XLFN replacement VLOOKUP array formula

In the example, the left lookup formula:

=VLOOKUP(14,CHOOSE({2,1},C3:C9,B3:B9),2,0)

Istvan Vozar

Istvan is the co-founder of Visual Analytics. He helps people reach the top in Excel.