Replace a missing match
| A | B | |
|---|---|---|
| 1 | Key | Value |
| 2 | A | 10 |
| 3 | B | 20 |
Formula: D2: =IFNA(VLOOKUP("C",A2:B3,2,FALSE),"Not found")
Result: Not found
The lookup returns #N/A, so IFNA supplies the message.
Google Sheets function
IFNA in Google Sheets
IFNA with worked examples you can test in the live grid. The argument details are in the Excel guide.
In Google Sheets
The arguments and common errors are covered in the IFNA Excel guide; check the function help in Google Sheets for its own argument list.
Worked examples in Google Sheets
Keep a found value
| A | B | |
|---|---|---|
| 1 | Key | Value |
| 2 | A | 10 |
| 3 | B | 20 |
Formula: D2: =IFNA(VLOOKUP("B",A2:B3,2,FALSE),"Not found")
Result: 20
A successful result passes through unchanged.
Other errors pass through
| A | |
|---|---|
| 1 | Value |
| 2 | 7 |
Formula: C2: =IFNA(A2/0,"fallback")
Result: #DIV/0!
Division by zero is not #N/A, so IFNA leaves the error visible.
Try it
A tiny spreadsheet with example data. Edit the formula or any number and see the result.
Formula in F2: =IFNA(MATCH("Zed",A2:A6,0),"not found")
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | Student | Score | Extra credit | Attempts | Result | |
| 2 | Ann | 92 | Yes | 2 | not found | |
| 3 | Bob | 74 | No | 0 | ||
| 4 | Cy | 58 | Yes | 3 | ||
| 5 | Dee | 81 | No | 1 | ||
| 6 | Eli | 66 | 2 |
Result in F2: not found
Google Sheets is used here only as a descriptive product name. FormulaCamp is not affiliated with Google.