Fix Excel 2016 Not Recognizing the SWITCH Function (#NAME Error)
Question details
The user is attempting to use the SWITCH function in Excel 2016, but the software fails to recognize it, excluding it from the function list and producing an error.
- Product
- Microsoft Excel 2016
- Device & OS
- not provided
- Scenario
- Writing a logical formula to evaluate an expression against a list of specific values to return a corresponding result.
- Observed behavior
- Excel returns a #NAME? error because the SWITCH function is unsupported in the 2016 version, and it does not appear in autocomplete.
Verify your exact Excel version by navigating to File > Account. If you are using a standalone Office 2016 license rather than Microsoft 365, you will need to adapt your workflow using alternative formulas.
Replace SWITCH with Nested IF Statements
Use a series of nested IF functions to replicate the exact logical behavior of the unsupported SWITCH function.
The IF function is universally supported across all versions of Excel. By nesting multiple IF statements, you can evaluate multiple conditions one after another, which achieves the exact same result as the SWITCH function.
Determine the exact values you want to evaluate and the specific results you want returned for each value.
Select the target cell and type your nested IF formula. For example, instead of =SWITCH(A1, 1, "Apples", 2, "Oranges", "Unknown"), type =IF(A1=1, "Apples", IF(A1=2, "Oranges", "Unknown")).
Press Enter to execute the formula. Double-check your parenthesis count at the end of the formula to ensure all nested statements are properly closed.
Use the CHOOSE Function as an Alternative
If your condition relies on sequential index numbers (1, 2, 3...), the CHOOSE function is a cleaner and fully supported alternative in Excel 2016.
Use Excel for the Web
Access your spreadsheet via the free online version of Excel to utilize the SWITCH function without upgrading your desktop software.
Experience Seamless Spreadsheet Compatibility with WPS Office
Don't let outdated Excel versions hold back your productivity. WPS Office provides a lightweight, highly capable spreadsheet alternative that effortlessly handles complex data and is fully compatible with Microsoft Excel formats. Enjoy a modern interface and rich formula support without the burden of expensive software upgrades.
- 1. Download WPS Office: Visit the official WPS website to download the free office suite directly to your computer.
- 2. Install and Launch: Run the quick installer, then open the WPS Spreadsheet application.
- 3. Open Your Workbook: Drag and drop your existing Excel file into WPS Spreadsheet to continue your work without missing out on modern formula calculation.
Frequently Asked Questions
Why do I get a #NAME? error with the SWITCH function?
The #NAME? error occurs because the SWITCH function is completely unsupported in Excel 2016. The software does not recognize the function's name and mistakenly assumes you are trying to type an undefined named range.
Is there an update or add-in to get the SWITCH function in Excel 2016?
No, Microsoft does not provide any official updates or add-ins to bring the SWITCH function to the standalone 2016 version. The function is strictly reserved for Excel 2019, Microsoft 365, and newer versions.
Does the IFS function work in Excel 2016?
No. Similar to the SWITCH function, the IFS function was introduced in later versions (Office 2019 and Microsoft 365) and is not supported in a standard Office 2016 installation.
