Fix the _xlfn Prefix for SWITCH in Excel 2016
Question details
The user needs to calculate a workbook containing SWITCH formulas in Excel 2016.

- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- A workbook created in Microsoft 365 is opened in Excel 2016.
- Observed behavior
- Excel 2016 displays the _xlfn prefix on SWITCH formulas and cannot calculate them.
In Excel, go to File > Account and check Product Information to confirm which Excel edition is installed. Select an affected cell and inspect the formula bar to identify the SWITCH expression, matching values, results, and any default result.
Replace SWITCH with a nested IF formula
Use nested IF when the workbook must calculate in Excel 2016.
A nested IF formula tests each possible value in order and returns a result for the first match. Include a final result for cases that do not match any listed value.
Select a cell showing an _xlfn.SWITCH formula and review its expression, value-result pairs, and default result in the formula bar.
In the formula bar, replace the SWITCH formula with nested IF tests. For example, replace =SWITCH(A2,1,"One",2,"Two","Other") with =IF(A2=1,"One",IF(A2=2,"Two","Other")).
Press Enter, then use the fill handle to apply the revised formula to other affected cells if appropriate. Test matching and nonmatching values to verify the results.

Open the workbook in a supported Excel version
Use this option to retain SWITCH formulas without rewriting them.
Work with compatible spreadsheet formulas in WPS Office
WPS Office is a free, lightweight alternative with a familiar spreadsheet interface. Open Excel workbooks in WPS Spreadsheets and use compatible formulas for easier migration; check formula results when moving between applications.

Frequently Asked Questions
What does _xlfn mean in an Excel formula?
The _xlfn prefix indicates that the Excel version opening the workbook does not recognize a function used in the formula. In Excel 2016, this can happen with SWITCH.
Can Excel 2016 calculate a SWITCH formula?
Perpetual Excel 2016 does not support SWITCH. Replace it with a compatible formula such as nested IF, or open the workbook in Excel 2019 or a supported Microsoft 365 version.
Why do two Excel installations with version 1902 behave differently?
The version number alone does not identify the product edition or its features. On each installation, check File > Account > Product Information to distinguish Excel 2016 from Microsoft 365.
How do I handle a default result when replacing SWITCH?
Put the default result in the final value-if-false argument of the nested IF formula. For example, =IF(A2=1,"One",IF(A2=2,"Two","Other")) returns "Other" when neither value matches.




