IFNA Function in Google Sheets: Formula, Examples & Troubleshooting

=IFNA(value, value_if_na)

Only #N/A errors trigger the replacement value; other errors remain visible. In formula syntax, square brackets mark optional arguments; do not type square brackets into an ordinary formula unless the particular function requires array notation. The separator shown is for the example locale and might change with spreadsheet settings.

When to use it: Use IFERROR only when all formula error types should share a recovery path.

Create a blank Google Sheets spreadsheet, enter the data as values (not quoted numeric strings), and use a clear output cell. Confirm File → Settings → Locale before comparing punctuation-sensitive formulas. These two examples use literal values, so you can enter them into blank cells without first constructing a table. Their answers are independent calculations; dates must be displayed with an actual date format.

=IFNA(NA(),"Not found")

Expected result: Not found. NA() produces an #N/A value, which is replaced with the supplied label.

1. Select an unused output cell (for example H2 if the practice table occupies A1:F9).
2. Enter the formula exactly as shown, including the opening equals sign and quoted text.
3. Compare the displayed answer to the expected result, then inspect the source cells if it differs.

For a range formula, the row numbers include data rows only unless the example explicitly refers to headers. A result that looks plausible may still be wrong if it references the wrong column.

=IFNA(12/3,"Not found")

Expected result: 4. The number 4 is returned unchanged because there is no #N/A.

This independent check can be entered in the next blank output cell; it helps distinguish a genuine calculation issue from a reference or paste-location mistake.

Watch out: IFNA is narrower than IFERROR: #DIV/0! and #REF! should not be silently swallowed.

Google Sheets may expand range outputs automatically and apply locale-aware parsing. A workbook imported from Excel can behave differently after conversion; recalculate the sample inside the target file.

In Sheets, a #N/A often indicates that a lookup found no match or a filter returned no matching row. #REF! may indicate that an array result could not expand; #ERROR! can indicate incorrect formula syntax. Open the cell error tooltip and confirm the spreadsheet locale.

If you get the wrong answer but no error, try these checks in order: (1) evaluate the simplified first example; (2) confirm the cell values and data types; (3) check the exact row and column boundaries; (4) verify quoted text, comparison operators and separators; (5) only then add more criteria or nested functions.

For a reproducible comparison with another spreadsheet product, use the same input values in both applications and record the installed version and locale. Different default matching behavior or handling of empty values can be a migration issue rather than a mathematical disagreement.

Google Sheets may use ARRAYFORMULA and different regex or sorting conventions than Excel. Do not paste a LibreOffice formula with semicolon argument separators into a US-locale Sheets file without reviewing the syntax.

Related guides already published for this function: Microsoft Excel | LibreOffice Calc | PlanMaker 2026

If a comparison tool says Not researched here, that means we do not yet have a checked guide for that software. It does not mean the software lacks this function. See the 12-program comparison tool for a guide-coverage matrix.

• Google Docs Editors Help — Google Sheets function list
• Function comparison tool
• How we verify function guides

Evidence level: Vendor-published function catalogue / formula overview, checked for this editorial batch on October 11, 2026; example outputs verified by direct reasoning/arithmetic where applicable. No execution in Google Sheets was performed. Some platform-, language- and release-specific edge cases remain for native testing.