Fix an Excel IFNA Formula That Adds a Plus Sign
Question details
The user needs to correct an IFNA formula that is treated as text or changed by inserting a plus sign instead of returning a calculated result.
- Product
- Microsoft Excel or WPS Spreadsheets
- Device & OS
- not provided
- Scenario
- The user is entering an IFNA formula with MATCH and wants the cell to return a match result or a fallback message such as "No Match!".
- Observed behavior
- The formula is displayed as text or Excel modifies it by adding a plus sign, so the calculation does not run.
Select the affected cell and check the formula bar to see whether the entry begins with an apostrophe or plus sign. Confirm that the cell format is set to General before re-entering the formula.
Set the cell to General and re-enter the IFNA formula with an equals sign
Use this solution when the formula is displayed as text, starts with an apostrophe, or gains a plus sign instead of calculating.
A spreadsheet recognizes a formula when it begins with an equals sign. General formatting allows the formula to be evaluated, while an apostrophe forces the entry to remain text.
Select the affected cell, open the Home tab, open the Number Format menu, and choose General.
Click the formula bar or press F2, then delete any leading apostrophe or plus sign before the formula.
Type the formula beginning with an equals sign: =IFNA(MATCH(A1,D1:D100,0),"No Match!")
Press Enter and verify that the cell returns the MATCH result or displays No Match! when A1 is not found in D1:D100.
Correct IFNA formulas in WPS Spreadsheets
WPS Spreadsheets lets you reset a cell to General format and enter the IFNA formula with the correct equals-sign syntax, so MATCH results and fallback messages calculate normally.
- 1. Open the workbook: Launch WPS Office, choose Spreadsheets, and open the workbook containing the IFNA formula.
- 2. Select General format: Select the formula cell, go to the Home tab, open the Number Format menu, and choose General.
- 3. Enter the corrected formula: Click the formula bar and enter =IFNA(MATCH(A1,D1:D100,0),"No Match!") without a leading apostrophe or plus sign.
- 4. Apply and verify: Press Enter and check that WPS Spreadsheets returns the match position or the No Match! fallback text.
Frequently Asked Questions
Why does my IFNA formula show as text instead of calculating?
The cell may be formatted as Text or the formula may begin with an apostrophe. Select the cell, choose Home > Number Format > General, remove the apostrophe, and re-enter the formula beginning with =.
Should an IFNA formula start with a plus sign?
No. Enter the formula with an equals sign, such as =IFNA(MATCH(A1,D1:D100,0),"No Match!"). A plus sign is not needed and may cause the entry to be handled incorrectly.
What does the MATCH portion of this IFNA formula do?
MATCH(A1,D1:D100,0) searches for the exact value in A1 within D1:D100. IFNA then returns No Match! when MATCH cannot find the value.
How can I fix the formula in WPS Spreadsheets?
Select the cell, choose Home > Number Format > General, remove any leading apostrophe or plus sign in the formula bar, and enter the formula beginning with =.
