Excel-IFERROR IFNA and SWITCH Practice Questions with Solutions

Introduction

Excel formulas sometimes return errors when data is missing, incorrect, or does not match the expected condition. IFERROR, IFNA, and SWITCH help you handle these situations and make worksheets easier to understand. In this chapter, you will practice these functions using different examples involving calculations, lookups, grades, departments, product categories, and status values. Excel-IFERROR IFNA and SWITCH practice questions with solutions to help you understand the concepts.

Question 1: Handle Division by Zero with IFERROR

Problem Statement

Calculate the sales per employee. Some departments have zero employees, which causes a #DIV/0! error. Use IFERROR to display "No Employees" instead of the error.

Excel Data

Solution

In D2, enter:

=IFERROR(B2/C2,"No Employees")

Copy the formula down.

Expected Output

Question 2: Handle Missing Lookup Results with IFNA

Problem Statement

Use VLOOKUP to find the price of each product. If a product does not exist in the price list, display "Product Not Found" instead of #N/A.

Price List

Search Data

Solution

Assume the price list is in F2:G6.

In B2, enter:

=IFNA(VLOOKUP(A2,$F$2:$G$6,2,FALSE),"Product Not Found")

Copy the formula down.

Expected Output

IFNA specifically handles the #N/A error.

Question 3: Use IFERROR with a Percentage Calculation

Problem Statement

Calculate the percentage of completed orders. Some records have zero total orders. Display "No Orders" when the calculation produces an error.

Excel Data

Solution

In D2, enter:

=IFERROR(B2/C2,"No Orders")

Format the result column as Percentage and copy the formula down.

Expected Output

Question 4: Use SWITCH to Convert Department Codes into Names

Problem Statement

A company stores department information using short codes:

• IT
• HR
• FN
• MK
• SL

Use SWITCH to display the full department name.

Excel Data

Solution

In C2, enter:

=SWITCH(B2,"IT","Information Technology","HR","Human Resources","FN","Finance","MK","Marketing","SL","Sales","Unknown")

Copy the formula down.

Expected Output

The final "Unknown" acts as the default result if none of the codes match.

Question 5: Use SWITCH to Convert Grades into Descriptions

Problem Statement

A school uses the following grade system:

Use SWITCH to automatically display the description.

Excel Data

Solution

In C2, enter:

=SWITCH(B2,"A","Excellent","B","Very Good","C","Good","D","Needs Improvement","F","Fail","Invalid Grade")

Copy down.

Expected Output

Question 6: Use IFERROR with a Discount Calculation

Problem Statement

Calculate the discount percentage for each order using:

Discount ÷ Original Price

Some orders have an original price of zero. Use IFERROR to display "Not Available".

Excel Data

Solution

In D2, enter:

=IFERROR(C2/B2,"Not Available")

Format the column as Percentage.

Expected Output

Question 7: Use IFNA with XLOOKUP

Problem Statement

Use XLOOKUP to find the employee’s department. If the employee ID does not exist, display "Employee Not Found".

Employee Data

Search Data

Solution

Assume employee IDs are in A2:A6 and departments are in C2:C6.

In B2, enter:

=IFNA(XLOOKUP(A2,$A$2:$A$6,$C$2:$C$6),"Employee Not Found")

Copy the formula down.

Expected Output

Question 8: Use SWITCH with Numeric Values

Problem Statement

A customer support system assigns priority levels using numbers:

• 1 = Low
• 2 = Medium
• 3 = High
• 4 = Critical

Use SWITCH to display the priority name.

Excel Data

Solution

In C2, enter:

=SWITCH(B2,1,"Low",2,"Medium",3,"High",4,"Critical","Invalid Code")

Copy down.

Expected Output

Question 9: Combine IFERROR with IF

Problem Statement

Calculate the average sales per order. If the number of orders is zero, display "No Orders". If the average is at least ₹5,000, display "Good"; otherwise display "Low".

Excel Data

Solution

In D2, enter:

=IFERROR(IF(B2/C2>=5000,"Good","Low"),"No Orders")

Copy down.

Expected Output

This example shows how IFERROR can wrap another formula containing IF.

Question 10: Build a Practical Order Status System with SWITCH and IFERROR

Problem Statement

Create an order status report using an order status code:

• 1 = Processing
• 2 = Shipped
• 3 = Delivered
• 4 = Cancelled

The system should display "Unknown Status" when an invalid code is entered.

Also calculate the Amount per Item using Total Amount ÷ Quantity. If Quantity is zero, display "Invalid Quantity".

Excel Data

Solution

Step 1: Convert Status Code

In E2, enter:

=SWITCH(B2,1,"Processing",2,"Shipped",3,"Delivered",4,"Cancelled","Unknown Status")

Copy down.

Step 2: Calculate Amount per Item

In F2, enter:

=IFERROR(D2/C2,"Invalid Quantity")

Copy down.

Expected Output

This question combines SWITCH and IFERROR in a practical order-management example.

Key Takeaways

• IFERROR lets you replace an Excel error with a meaningful result.
• IFNA specifically handles the #N/A error.
• SWITCH compares one value against multiple possible values.
• IFERROR is useful for calculations involving division by zero.
• IFNA is especially useful with lookup functions such as VLOOKUP and XLOOKUP.
• SWITCH can work with both text and numeric values.
• A default value can be added to SWITCH when none of the cases match.
• IFERROR can contain another IF formula.
• These functions can make reports cleaner and easier for other users to understand.
• Error handling should provide a meaningful message instead of simply hiding problems in the data.

FAQs

1. What is IFERROR in Excel?

IFERROR checks whether a formula produces an error. If an error occurs, it returns the value you specify.

=IFERROR(A2/B2,"Not Available")

2. What is IFNA in Excel?

IFNA handles the #N/A error specifically.

=IFNA(VLOOKUP(A2,F:G,2,FALSE),"Not Found")

3. What is the difference between IFERROR and IFNA?

IFERROR handles many types of Excel errors, while IFNA handles only the #N/A error.

4. When should I use IFNA instead of IFERROR?

Use IFNA when #N/A is the specific error you expect, especially when working with lookup formulas.

5. What does SWITCH do in Excel?

SWITCH compares one expression against several values and returns the result associated with the matching value.

=SWITCH(A2,1,"Low",2,"Medium",3,"High","Unknown")

6. Can SWITCH use text values?

Yes. For example:

=SWITCH(A2,"IT","Technology","HR","Human Resources","Other")

7. Can SWITCH use numbers?

Yes. Numeric values can be used as the cases in a SWITCH formula.

8. Can IFERROR be combined with IF?

Yes. For example:

=IFERROR(IF(B2/C2>=5000,"Good","Low"),"No Orders")

9. Can IFNA be used with XLOOKUP?

Yes. It can be used to replace the #N/A result when XLOOKUP cannot find a matching value.

10. Why should I use error-handling functions in Excel?

They prevent confusing error messages from appearing in reports and allow you to display useful messages such as "Not Found", "No Orders", or "Invalid Quantity".

Written by Shubhranshu Shekhar, who has trained 20000+ students in coding.

Rishabh Kumar