Function Problems

Fix the _xlfn Prefix for SWITCH in Excel 2016

Kushani NimanthikaKushani Nimanthika Nov 14, 2026 868 views

Question details

The user needs to calculate a workbook containing SWITCH formulas in Excel 2016.

Fix the _xlfn Prefix for SWITCH 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.
Before you start

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.

Solution 1Recommended

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.

1
Inspect the formula

Select a cell showing an _xlfn.SWITCH formula and review its expression, value-result pairs, and default result in the formula bar.

2
Enter an IF equivalent

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")).

3
Apply and test

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.

Replace SWITCH with a nested IF formula
Adapt the formula: Use the actual cell references, comparisons, and results from your workbook. If the original SWITCH formula has no default result, decide how the replacement should handle unmatched values.
Free Microsoft Office alternative

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.

Edit spreadsheet formulas in a familiar interfaceOpen and save Microsoft Excel XLSX and XLS filesUse nested IF formulas for workbooks that need broader compatibilityMove existing Office documents into a lightweight, easy-to-use suite
microsoft office alternative - wps office

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.