0
0
Google Sheetsspreadsheet~10 mins

IFERROR and IFNA in Google Sheets - Cell-by-Cell Formula Trace

Choose your learning style9 modes available
LearnWhyDeepSheetTryChallengeScenarioRecallDash
Sample Data

This sheet shows division operations and VLOOKUPs that may cause errors or #N/A, demonstrating how IFERROR and IFNA handle these cases.

CellValue
A110
A20
A35
B1=A1/A3
B2=A1/A2
C1=IFERROR(A1/A3, "Error")
C2=IFERROR(A1/A2, "Error")
D1=IFNA(VLOOKUP(1, {1, "One"; 2, "Two"; 3, "Three"}, 2, FALSE), "Not found")
D2=IFNA(VLOOKUP(4, {1, "One"; 2, "Two"; 3, "Three"}, 2, FALSE), "Not found")
Formula Trace
=IFERROR(A1/A2, "Error")
Step 1: A1/A2
Step 2: IFERROR(#DIV/0!, "Error")
Cell Reference Map
    A      B           C           D
1 | 10 | =A1/A3 | =IFERROR(A1/A3, "Error") | =IFNA(VLOOKUP(1, ...), "Not found")
2 |  0 | =A1/A2 | =IFERROR(A1/A2, "Error") | =IFNA(VLOOKUP(4, ...), "Not found")
3 |  5 |        |                         |
Cells A1, A2, A3 provide numbers. B1 and B2 calculate divisions. C1 and C2 use IFERROR to handle errors in division. D1 and D2 use IFNA to handle #N/A from VLOOKUP.
Result
    A    |    B    |      C      |       D       
-------------------------------------------------
1 |  10   |    2    |      2      |     One       
2 |   0   | #DIV/0! |    Error    |  Not found    
3 |   5   |         |             |
B1 shows 10 divided by 5 = 2. B2 shows division by zero error. C1 shows 2 because no error. C2 shows 'Error' because IFERROR caught the division error. D1 finds 1 in VLOOKUP and returns 'One'. D2 does not find 4, so IFNA returns 'Not found'.
Sheet Trace Quiz - 3 Questions
Test your understanding
What does IFERROR(A1/A2, "Error") return if A1=10 and A2=0?
A#DIV/0!
B0
CError
D10
Key Result
IFERROR(expression, value_if_error) returns expression result or value_if_error if any error occurs; IFNA(expression, value_if_na) returns expression result or value_if_na only if #N/A error occurs.