Lookup with IFERROR Excel Practice Question with Solutions

Introduction

Excel lookups become more useful when your data is incomplete, contains partial text, or requires approximate matching. In this chapter, you will practice IFERROR, wildcard characters such as * and ?, and approximate matching with VLOOKUP, XLOOKUP, and MATCH. These practical questions cover employee records, product searches, grading systems, commission rates, tax slabs, and sales data. Lookup with IFERROR Excel Practice Question with Solutions to help you understand the concepts.

Question 1: Handle a Missing Product with IFERROR and VLOOKUP

Problem Statement

Find the price of each product using VLOOKUP. If a product does not exist, display "Product Not Found" instead of #N/A.

Excel Data

Search Data

Excel Solution

In B9, enter:

=IFERROR(VLOOKUP(A9,$A$2:$C$6,3,FALSE),"Product Not Found")

Copy the formula down.

Expected Output

Concepts Covered

• VLOOKUP
• IFERROR
• Exact match
• Handling #N/A

Question 2: Use XLOOKUP with IFERROR-Style Error Handling

Problem Statement

Find an employee’s department using their Employee ID. If the ID does not exist, display "Employee Not Found".

Excel Data

Search Data

Excel Solution

In B9, enter:

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

Copy the formula down.

Expected Output

XLOOKUP has its own if_not_found argument, so IFERROR is not required for this particular case.

Question 3: Find Products Using a Wildcard

Problem Statement

Find the first product whose name starts with "Key".

Excel Data

Search Data

Excel Solution

Using XLOOKUP, enter:

=XLOOKUP(A9&"*",$B$2:$B$6,$B$2:$B$6,"Not Found",2)

Copy the formula down.

Expected Output

The * wildcard means any number of characters can appear after the search text.

For example:

Key*

can match:

• Keyboard
• Keypad

XLOOKUP returns the first matching result.

Question 4: Search for Text Containing a Specific Word

Problem Statement

Find the first product whose name contains "Pro" anywhere in the product name.

Excel Data

Search Data

Excel Solution

In B9, enter:

=XLOOKUP("*"&A9&"*",$B$2:$B$6,$B$2:$B$6,"Not Found",2)

Copy down.

Expected Output

The formula:

"*"&A9&"*"

allows characters to appear before and after the search text.

Question 5: Use the ? Wildcard

Problem Statement

Use the ? wildcard to find product codes where one character can vary.

Excel Data

Search Data

Excel Solution

In B9, enter:

=XLOOKUP(A9,$A$2:$A$6,$B$2:$B$6,"Not Found",2)

Copy down.

Expected Output

The ? wildcard represents exactly one character.

For example:

AB10?

can match:

• AB101
• AB102
• AB103

Question 6: Approximate Match for Commission Rates

Problem Statement

A company gives different commission rates based on sales amount.

Find the commission rate for each salesperson.

Commission Table

Sales Data

Excel Solution

In C9, enter:

=VLOOKUP(B9,$A$2:$B$6,2,TRUE)

Copy down.

Expected Output

Important

For approximate VLOOKUP, the first column of the lookup table must be sorted in ascending order.

For example:

0
25000
50000
75000
100000

Question 7: Approximate Match for Student Grades

Problem Statement

Assign a grade based on a student’s marks.

Grade Table

Student Data

Excel Solution

In C9, enter:

=VLOOKUP(B9,$A$2:$B$7,2,TRUE)

Copy down.

Expected Output

Concepts Covered

• Approximate matching
• Grading system
• VLOOKUP
• Sorted lookup table

Question 8: Approximate MATCH for a Tax Slab

Problem Statement

Find the applicable tax slab based on annual income.

Tax Table

Income Data

Excel Solution

Use INDEX and approximate MATCH:

=INDEX($B$2:$B$6,MATCH(B9,$A$2:$A$6,1))

Copy down.

Expected Output

The 1 in MATCH finds the largest value that is less than or equal to the lookup value.

Question 9: Approximate XLOOKUP for Delivery Charges

Problem Statement

A delivery company charges different fees based on package weight.

Find the delivery charge for each package.

Delivery Table

Package Data

Excel Solution

In C9, enter:

=XLOOKUP(B9,$A$2:$A$6,$B$2:$B$6,,-1)

Copy down.

Expected Output

The -1 match mode tells XLOOKUP to find an exact match or the next smaller value.

Question 10: Combine Wildcard Lookup, IFERROR and XLOOKUP

Problem Statement

Create a product search system where the user enters part of a product name.

The formula should:

• Find a product containing the entered text.
• Return its price.
• Display "Product Not Found" if there is no match.

Excel Data

Search Data

Excel Solution

In B9, enter:

=IFERROR(XLOOKUP("*"&A9&"*",$B$2:$B$6,$D$2:$D$6,"Product Not Found",2),"Product Not Found")

Copy down.

Expected Output

Concepts Covered

• XLOOKUP
• Wildcard search
• * wildcard
• IFERROR
• Partial-text lookup
• Error handling
• Practical product search

Key Takeaways

• IFERROR can replace lookup errors with a useful message.
• XLOOKUP has an if_not_found argument, so IFERROR is not always necessary.
• The * wildcard represents zero or more characters.
• The ? wildcard represents exactly one character.
• Wildcards are useful when you know only part of a text value.
• VLOOKUP(...,TRUE) can perform approximate matching.
• Approximate lookup tables must be sorted correctly.
• MATCH(...,1) can perform approximate matching when the lookup table is sorted in ascending order.
• XLOOKUP can use -1 to find an exact match or the next smaller value.
• Approximate matching is useful for grades, commissions, tax slabs, delivery charges, discounts, and pricing tiers.
• Wildcard lookup is useful for product searches, customer searches, employee names, and partially known text.

FAQs

1. What is a wildcard in Excel?

A wildcard is a special character that allows Excel to search for patterns instead of requiring an exact text match.

The two main wildcards are:

*
?

2. What does * mean in Excel?

* represents any number of characters.

For example:

"*phone*"

can find text containing "phone" anywhere in the cell.

3. What does ? mean in Excel?

? represents exactly one character.

For example:

AB10?

can match:

AB101
AB102
AB103

4. What is approximate matching in Excel?

Approximate matching finds the closest applicable value instead of requiring an exact match.

For example, a sales amount of ₹68,000 can fall into the ₹50,000 commission slab.

5. When should I use approximate VLOOKUP?

Approximate VLOOKUP is useful when you have ranges or slabs such as:

• Marks and grades
• Sales and commissions
• Income and tax rates
• Weight and delivery charges
• Purchase amount and discounts

6. Does approximate VLOOKUP require sorted data?

Yes. When using:

=VLOOKUP(A2,table,2,TRUE)

the first column of the lookup table should normally be sorted in ascending order.

7. What is approximate MATCH?

Approximate MATCH can find the position of the largest value that is less than or equal to the lookup value when using:

=MATCH(A2,B2:B10,1)

The lookup range needs to be sorted in ascending order.

8. Can XLOOKUP use wildcards?

Yes. With the appropriate match mode, XLOOKUP can perform wildcard matching.

For example:

=XLOOKUP("*"&A2&"*",B2:B10,C2:C10,"Not Found",2)

9. What is the difference between * and ??

* can represent zero, one, or many characters.

? represents exactly one character.

10. Can IFERROR be used with XLOOKUP in Excel?

Yes, although XLOOKUP already provides an if_not_found argument.

For example:

=XLOOKUP(A2,B2:B10,C2:C10,"Not Found")

is often enough to handle a missing lookup value.

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

Rishabh Kumar