XLFN

The XLFN prefix in front of a formula indicates that the formula uses a function not available in your current version of Excel.

If you open a workbook you created in 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.

An _xlfn. prefix is displayed in front of a formula

In the example, we use our favorite lookup function, XLOOKUP. It is 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 replacement for the left lookup. Alternatively, you can use the VLOOKUP and CHOOSE combination to perform a left lookup. The demonstrated formula below works fine with Excel 2013, 2016, and 2019.

XLFN-replacement-VLOOKUP-array-formula

In the example, the left lookup formula:

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