IFNA and IFERROR Functions

 

Step-by-Step Process to Use IFNA and IFERROR Functions

🔹 Method 1: Using IFNA to Handle #N/A Errors

Scenario: Lookup values that might not exist

Let's say we have a Product List and we want to check the price using VLOOKUP.

🔹 Sample Data (A1:B5)

Product	Price (₹)
Shoes	500
Bags	300
Shirts	400
Jeans	600

🔹 Formula to Lookup Price in Cell D2 

=VLOOKUP(D2, A1:B5, 2, FALSE)

If we search for a product that doesn't exist (e.g., "Hats"), we get #N/A.

✔ Solution: Using IFNA to Replace #N/A 

=IFNA(VLOOKUP(D2, A1:B5, 2, FALSE), "Not Found")

➡ If the product is not found, it will return "Not Found" instead of #N/A.

---

🔹 Method 2: Using IFERROR to Handle Multiple Errors

Scenario: Preventing Division Errors

Let's say we have Total Sales and Number of Customers.

🔹 Sample Data (A1:B5)

Sales (₹)	Customers
10000	50
5000	25
12000	0
8000	40

🔹 Formula to Calculate Average Sales Per Customer 

=A2/B2

If B3 contains 0, Excel returns a #DIV/0! error.

✔ Solution: Using IFERROR to Handle Errors 

=IFERROR(A2/B2, "Invalid Data")

➡ If there’s an error (like division by zero), it returns "Invalid Data" instead of showing #DIV/0!.

---

🎯 Key Takeaways

🔹 Use IFNA when dealing with #N/A errors from lookup functions like VLOOKUP, HLOOKUP, or XLOOKUP.
🔹 Use IFERROR to catch all errors (#N/A, #VALUE!, #DIV/0!, #REF!, etc.).
🔹 Enhances reports by replacing errors with meaningful messages.

Would you like me to create an Excel file with sample formulas for practice? 😊