Written and reviewed by FormulaCraft Team. Each formula on this page is run through our verification engine before publishing.
Last reviewed:
Fix the IF error
Excel & Google SheetsWhat IF means
When an IF formula is not working, it either returns the wrong branch (true when false is expected or vice versa), returns a #VALUE! error, or always returns the same branch regardless of the test value. The logical_test is the most common source of bugs — it evaluates to an unexpected TRUE or FALSE.
Common causes
- Testing a text value with = against a number or vice versa — =IF(A2=1,...) when A2 contains "1" (text) always evaluates the false branch.
- Using = instead of == is not an issue (both work), but using a single = when a comparative operator like >= or <= was intended gives wrong results.
- An empty cell test written as =IF(A2="",...) accidentally matches cells with a formula returning an empty string, not just truly blank cells.
- The value_if_true or value_if_false contains text without quotes — unquoted text is interpreted as a named range and causes #NAME? or #VALUE!.
- The logical_test compares floating-point numbers with = which can fail due to floating-point precision — two 'equal' results of arithmetic may differ by a tiny fraction.
Example fix
Broken
=IF(A2=1,Pass,Fail)Fixed
=IF(A2=1,"Pass","Fail")Pass and Fail without quotes are interpreted as named ranges which do not exist, causing #NAME?. Wrapping them in double quotes makes them text literals.
Try it with your data
Edit the grid or formula, then run it through a real spreadsheet engine — no signup.
Sample data — click any cell to edit
Runs server-side · free · no signup
How to fix it
- 1Check data types: use =ISNUMBER(A2) or =ISTEXT(A2) to confirm what the test cell contains before writing the IF. Match the criteria type to the cell type.
- 2Put text values in the value_if_true and value_if_false arguments in double quotes: =IF(A2>10,"High","Low") — never =IF(A2>10,High,Low).
- 3To test for a truly blank cell use =ISBLANK(A2) rather than A2="", which also matches empty-string formula results.
- 4For floating-point comparisons, use ROUND: =IF(ROUND(A2,2)=ROUND(B2,2),"Match","No match") instead of a direct = comparison.
- 5Add an intermediate column with just the logical_test (=A2>10) to see whether it evaluates TRUE or FALSE as expected before building the full IF.
Stop hunting errors by hand.
Upload your spreadsheet and the Auditor flags every IF and broken formula at once — or paste this one formula and get the fix explained.
Frequently asked
My IF formula always returns the false result even when the value looks correct — why?
The cell likely contains a number stored as text. =IF(A2=100,...) will go to false if A2 holds the text "100". Use =IF(VALUE(A2)=100,...) or convert the column to numbers.
How do I test for multiple conditions in one IF?
Wrap conditions in AND() or OR(): =IF(AND(A2>0,B2="Active"),"Yes","No"). For many conditions, consider IFS which avoids deep nesting.
Can IF return a formula result rather than a text string?
Yes. The value_if_true and value_if_false can be any valid expression: =IF(A2>0,SUM(B2:B10),AVERAGE(C2:C10)). There is no need to quote formula results.