IFERROR and IFNA in Google Sheets - Practice Problems & Coding Challenges

Challenge - 5 Problems

📊 Formula Result

intermediate

Output of IFERROR with division by zero

What is the output of the formula =IFERROR(10/0, "Error") in Google Sheets?

Google Sheets

=IFERROR(10/0, "Error")

A Error

B #DIV/0!

C 0

D 10

💡 Hint

IFERROR returns the second value if the first causes an error.

The formula tries to divide 10 by 0, which causes a division error. IFERROR catches this and returns the second argument, "Error".

📊 Formula Result

intermediate

Output of IFNA with VLOOKUP missing value

Given a table where A2:A4 contains names and B2:B4 contains ages, what is the output of =IFNA(VLOOKUP("John", A2:B4, 2, FALSE), "Not found") if "John" is not in the list?

Google Sheets

=IFNA(VLOOKUP("John", A2:B4, 2, FALSE), "Not found")

A #REF!

B #N/A

C 0

D Not found

💡 Hint

IFNA returns the second value only if the first returns #N/A error.

VLOOKUP returns #N/A when it can't find "John". IFNA catches this and returns "Not found".

❓ Function Choice

advanced

Choosing between IFERROR and IFNA

You want to catch only #N/A errors from a formula but let other errors show normally. Which function should you use?

A ISNA

B IFNA

C ISERROR

D IFERROR

💡 Hint

IFNA only catches #N/A errors, IFERROR catches all errors.

IFNA catches only #N/A errors and leaves other errors visible. IFERROR catches all errors.

🎯 Scenario

advanced

Handling errors in nested formulas

You have a formula =A1/B1 that sometimes causes division errors. You want to show "Divide by zero" if B1 is zero, and "Error" for any other error. Which formula achieves this?

A =IFERROR(A1/B1, IF(B1=0, "Divide by zero", "Error"))

B =IFERROR(IF(B1=0, "Divide by zero", A1/B1), "Error")

C =IF(B1=0, "Divide by zero", IFERROR(A1/B1, "Error"))

D =IFNA(A1/B1, "Divide by zero")

💡 Hint

Check the condition for zero first, then handle other errors.

Option C first checks if B1 is zero and returns "Divide by zero". Otherwise, it tries A1/B1 and catches other errors with IFERROR returning "Error".

❓ data_analysis

expert

Counting errors handled by IFERROR

Given a column C2:C10 with formulas that may produce errors, which formula counts how many cells return an error that IFERROR would catch?

A =SUMPRODUCT(--ISERROR(C2:C10))

B =COUNTIF(C2:C10, "#N/A")

C =COUNTIF(C2:C10, "#DIV/0!")

D =SUMPRODUCT(--ISNA(C2:C10))

💡 Hint

IFERROR catches all errors, not just #N/A.

ISERROR detects all error types. SUMPRODUCT with double unary counts how many cells have errors. COUNTIF with specific error text counts only that error type.