Excel Error functions IFERROR, ISERROR, ISERR, IFNA, and ISNA. 20 examples of each

 Excel provides several error-handling functions that help manage and handle errors in formulas. Below are 20 examples for each of the most commonly used error functions: IFERROR, ISERROR, ISERR, IFNA, and ISNA.

---

1. IFERROR Function

The IFERROR function returns a custom value if a formula results in an error; otherwise, it returns the result of the formula.

Examples:

1. =IFERROR(A1/B1, "Division by zero error")
   Returns "Division by zero error" if B1 is 0.

2. =IFERROR(VLOOKUP("Apple", A1:B10, 2, FALSE), "Not found")
   Returns "Not found" if "Apple" is not in the range.

3. =IFERROR(SQRT(-1), "Invalid input")
   Returns "Invalid input" because the square root of a negative number is an error.

4. =IFERROR(1/0, "Infinity")
   Returns "Infinity" because division by zero is an error.

5. =IFERROR(MATCH("Banana", A1:A10, 0), "Not in list")
   Returns "Not in list" if "Banana" is not found.

6. =IFERROR(DATE(2023, 13, 1), "Invalid date")
   Returns "Invalid date" because month 13 is invalid.

7. =IFERROR(INDEX(A1:A10, 11), "Out of range")
   Returns "Out of range" because the index is beyond the range.

8. =IFERROR(LEFT("Text", -1), "Invalid length")
   Returns "Invalid length" because the length is negative.

9. =IFERROR(FIND("X", "Hello"), "Not found")
   Returns "Not found" because "X" is not in "Hello".

10. =IFERROR(TEXT("ABC", "0.00"), "Invalid format")
    Returns "Invalid format" because "ABC" cannot be converted to a number.

11. =IFERROR(NETWORKDAYS("2023-02-30", "2023-03-01"), "Invalid date")
    Returns "Invalid date" because "2023-02-30" is invalid.

12. =IFERROR(INDIRECT("Sheet2!A1"), "Sheet not found")
    Returns "Sheet not found" if "Sheet2" does not exist.

13. =IFERROR(ROUND("Text", 2), "Invalid number")
    Returns "Invalid number" because "Text" is not a number.

14. =IFERROR(POWER(2, "A"), "Invalid exponent")
    Returns "Invalid exponent" because "A" is not a number.

15. =IFERROR(TIME(25, 0, 0), "Invalid time")
    Returns "Invalid time" because 25 is not a valid hour.

16. =IFERROR(SUM("Text"), "Invalid input")
    Returns "Invalid input" because "Text" cannot be summed.

17. =IFERROR(LOG(0), "Undefined")
    Returns "Undefined" because the logarithm of 0 is undefined.

18. =IFERROR(CHOOSE(5, "A", "B", "C"), "Out of range")
    Returns "Out of range" because 5 is beyond the number of choices.

19. =IFERROR(REPLACE("Text", 0, 1, "X"), "Invalid position")
    Returns "Invalid position" because 0 is not a valid start position.

20. =IFERROR(SUBSTITUTE("Text", "X", "Y"), "No match")
    Returns "No match" if "X" is not found in "Text".

21. 
    

---

2. ISERROR Function

The ISERROR function checks if a formula results in any error and returns TRUE or FALSE.

Examples:

1. =ISERROR(A1/B1)
   Returns TRUE if B1 is 0.

2. =ISERROR(VLOOKUP("Apple", A1:B10, 2, FALSE))
   Returns TRUE if "Apple" is not found.

3. =ISERROR(SQRT(-1))
   Returns TRUE because the square root of a negative number is an error.

4. =ISERROR(1/0)
   Returns TRUE because division by zero is an error.

5. =ISERROR(MATCH("Banana", A1:A10, 0))
   Returns TRUE if "Banana" is not found.

6. =ISERROR(DATE(2023, 13, 1))
   Returns TRUE because month 13 is invalid.

7. =ISERROR(INDEX(A1:A10, 11))
   Returns TRUE because the index is beyond the range.

8. =ISERROR(LEFT("Text", -1))
   Returns TRUE because the length is negative.

9. =ISERROR(FIND("X", "Hello"))
   Returns TRUE because "X" is not in "Hello".

10. =ISERROR(TEXT("ABC", "0.00"))
    Returns TRUE because "ABC" cannot be converted to a number.

11. =ISERROR(NETWORKDAYS("2023-02-30", "2023-03-01"))
    Returns TRUE because "2023-02-30" is invalid.

12. =ISERROR(INDIRECT("Sheet2!A1"))
    Returns TRUE if "Sheet2" does not exist.

13. =ISERROR(ROUND("Text", 2))
    Returns TRUE because "Text" is not a number.

14. =ISERROR(POWER(2, "A"))
    Returns TRUE because "A" is not a number.

15. =ISERROR(TIME(25, 0, 0))
    Returns TRUE because 25 is not a valid hour.

16. =ISERROR(SUM("Text"))
    Returns TRUE because "Text" cannot be summed.

17. =ISERROR(LOG(0))
    Returns TRUE because the logarithm of 0 is undefined.

18. =ISERROR(CHOOSE(5, "A", "B", "C"))
    Returns TRUE because 5 is beyond the number of choices.

19. =ISERROR(REPLACE("Text", 0, 1, "X"))
    Returns TRUE because 0 is not a valid start position.

20. =ISERROR(SUBSTITUTE("Text", "X", "Y"))
    Returns TRUE if "X" is not found in "Text".

21. 
    

---

3. ISERR Function

The ISERR function checks if a formula results in any error except #N/A and returns TRUE or FALSE.

Examples:

1. =ISERR(A1/B1)
   Returns TRUE if B1 is 0.

2. =ISERR(VLOOKUP("Apple", A1:B10, 2, FALSE))
   Returns TRUE if "Apple" is not found.

3. =ISERR(SQRT(-1))
   Returns TRUE because the square root of a negative number is an error.

4. =ISERR(1/0)
   Returns TRUE because division by zero is an error.

5. =ISERR(MATCH("Banana", A1:A10, 0))
   Returns TRUE if "Banana" is not found.

6. =ISERR(DATE(2023, 13, 1))
   Returns TRUE because month 13 is invalid.

7. =ISERR(INDEX(A1:A10, 11))
   Returns TRUE because the index is beyond the range.

8. =ISERR(LEFT("Text", -1))
   Returns TRUE because the length is negative.

9. =ISERR(FIND("X", "Hello"))
   Returns TRUE because "X" is not in "Hello".

10. =ISERR(TEXT("ABC", "0.00"))
    Returns TRUE because "ABC" cannot be converted to a number.

11. =ISERR(NETWORKDAYS("2023-02-30", "2023-03-01"))
    Returns TRUE because "2023-02-30" is invalid.

12. =ISERR(INDIRECT("Sheet2!A1"))
    Returns TRUE if "Sheet2" does not exist.

13. =ISERR(ROUND("Text", 2))
    Returns TRUE because "Text" is not a number.

14. =ISERR(POWER(2, "A"))
    Returns TRUE because "A" is not a number.

15. =ISERR(TIME(25, 0, 0))
    Returns TRUE because 25 is not a valid hour.

16. =ISERR(SUM("Text"))
    Returns TRUE because "Text" cannot be summed.

17. =ISERR(LOG(0))
    Returns TRUE because the logarithm of 0 is undefined.

18. =ISERR(CHOOSE(5, "A", "B", "C"))
    Returns TRUE because 5 is beyond the number of choices.

19. =ISERR(REPLACE("Text", 0, 1, "X"))
    Returns TRUE because 0 is not a valid start position.

20. =ISERR(SUBSTITUTE("Text", "X", "Y"))
    Returns TRUE if "X" is not found in "Text".

21. 
    

---

4. IFNA Function

The IFNA function returns a custom value if a formula results in #N/A; otherwise, it returns the result of the formula.

Examples:

1. =IFNA(VLOOKUP("Apple", A1:B10, 2, FALSE), "Not found")
   Returns "Not found" if "Apple" is not in the range.

2. =IFNA(MATCH("Banana", A1:A10, 0), "Not in list")
   Returns "Not in list" if "Banana" is not found.

3. =IFNA(INDEX(A1:A10, 11), "Out of range")
   Returns "Out of range" because the index is beyond the range.

4. =IFNA(FIND("X", "Hello"), "Not found")
   Returns "Not found" because "X" is not in "Hello".

5. =IFNA(INDIRECT("Sheet2!A1"), "Sheet not found")
   Returns "Sheet not found" if "Sheet2" does not exist.

6. =IFNA(CHOOSE(5, "A", "B", "C"), "Out of range")
   Returns "Out of range" because 5 is beyond the number of choices.

7. =IFNA(SUBSTITUTE("Text", "X", "Y"), "No match")
   Returns "No match" if "X" is not found in "Text".

8. =IFNA(HLOOKUP("Apple", A1:B10, 2, FALSE), "Not found")
   Returns "Not found" if "Apple" is not in the range.

9. =IFNA(XLOOKUP("Apple", A1:A10, B1:B10), "Not found")
   Returns "Not found" if "Apple" is not in the range.

10. =IFNA(FILTER(A1:A10, B1:B10="Apple"), "No match")
    Returns "No match" if no rows match the condition.

11. =IFNA(UNIQUE(FILTER(A1:A10, B1:B10="Apple")), "No unique values")
    Returns "No unique values" if no rows match the condition.

12. =IFNA(SORT(FILTER(A1:A10, B1:B10="Apple")), "No data")
    Returns "No data" if no rows match the condition.

13. =IFNA(SEQUENCE(0), "Invalid sequence")
    Returns "Invalid sequence" because the sequence length is 0.

14. =IFNA(RANDARRAY(0, 0), "Invalid array")
    Returns "Invalid array" because the array dimensions are invalid.

15. =IFNA(SPILL("A1:A10"), "No spill range")
    Returns "No spill range" if the spill range is invalid.

16. =IFNA(LET(x, 1/0, x), "Error in calculation")
    Returns "Error in calculation" because of division by zero.

17. =IFNA(LAMBDA(x, x^2)("Text"), "Invalid input")
    Returns "Invalid input" because "Text" cannot be squared.

18. =IFNA(BYROW(A1:A10, LAMBDA(x, x^2)), "Error in calculation")
    Returns "Error in calculation" if any cell in the range contains an error.

19. =IFNA(BYCOL(A1:B10, LAMBDA(x, SUM(x))), "Error in calculation")
    Returns "Error in calculation" if any column contains an error.

20. =IFNA(MAP(A1:A10, LAMBDA(x, x^2)), "Error in calculation")
    Returns "Error in calculation" if any cell in the range contains an error.

21. 
    

---

5. ISNA Function

The ISNA function checks if a formula results in #N/A and returns TRUE or FALSE.

Examples:

1. =ISNA(VLOOKUP("Apple", A1:B10, 2, FALSE))
   Returns TRUE if "Apple" is not found.

2. =ISNA(MATCH("Banana", A1:A10, 0))
   Returns TRUE if "Banana" is not found.

3. =ISNA(INDEX(A1:A10, 11))
   Returns TRUE because the index is beyond the range.

4. =ISNA(FIND("X", "Hello"))
   Returns TRUE because "X" is not in "Hello".

5. =ISNA(INDIRECT("Sheet2!A1"))
   Returns TRUE if "Sheet2" does not exist.

6. =ISNA(CHOOSE(5, "A", "B", "C"))
   Returns TRUE because 5 is beyond the number of choices.

7. =ISNA(SUBSTITUTE("Text", "X", "Y"))
   Returns TRUE if "X" is not found in "Text".

8. =ISNA(HLOOKUP("Apple", A1:B10, 2, FALSE))
   Returns TRUE if "Apple" is not in the range.

9. =ISNA(XLOOKUP("Apple", A1:A10, B1:B10))
   Returns TRUE if "Apple" is not in the range.

10. =ISNA(FILTER(A1:A10, B1:B10="Apple"))
    Returns TRUE if no rows match the condition.

11. =ISNA(UNIQUE(FILTER(A1:A10, B1:B10="Apple")))
    Returns TRUE if no rows match the condition.

12. =ISNA(SORT(FILTER(A1:A10, B1:B10="Apple")))
    Returns TRUE if no rows match the condition.

13. =ISNA(SEQUENCE(0))
    Returns TRUE because the sequence length is 0.

14. =ISNA(RANDARRAY(0, 0))
    Returns TRUE because the array dimensions are invalid.

15. =ISNA(SPILL("A1:A10"))
    Returns TRUE if the spill range is invalid.

16. =ISNA(LET(x, 1/0, x))
    Returns TRUE because of division by zero.

17. =ISNA(LAMBDA(x, x^2)("Text"))
    Returns TRUE because "Text" cannot be squared.

18. =ISNA(BYROW(A1:A10, LAMBDA(x, x^2)))
    Returns TRUE if any cell in the range contains an error.

19. =ISNA(BYCOL(A1:B10, LAMBDA(x, SUM(x))))
    Returns TRUE if any column contains an error.

20. =ISNA(MAP(A1:A10, LAMBDA(x, x^2)))
    Returns TRUE if any cell in the range contains an error.

---

These examples demonstrate how to use Excel's error-handling functions effectively