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
If we search for a product that doesn't exist (e.g., "Hats"), we get #N/A.
✔ Solution: Using IFNA to Replace #N/A
➡ 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
If B3 contains 0, Excel returns a #DIV/0! error.
✔ Solution: Using IFERROR to Handle Errors
➡ 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? 😊