VLOOKUP, HLOOKUP and XLOOKUP Practice Questions With Solutions

Introduction

Lookup functions help you find information from a table using a matching value. VLOOKUP, HLOOKUP, and XLOOKUP are commonly used for employee records, product prices, student marks, customer data, inventory, and reports. In this chapter, you will practice all three lookup functions through different questions, starting with simple lookups and moving toward more practical combinations. VLOOKUP, HLOOKUP and XLOOKUP Practice questions With solutions to help you understand the concepts.

Question 1: Find an Employee’s Department Using VLOOKUP

Problem Statement

Use the Employee ID to find the department of each employee.

Excel Data

Employee Table

Search Table

Solution

If the employee table is in A2:D6, enter this formula in F2:

=VLOOKUP(E2,$A$2:$D$6,3,FALSE)

Copy down.

Expected Output

3 tells Excel to return the value from the third column of the lookup table.

Question 2: Find Product Price Using VLOOKUP

Problem Statement

Use the Product ID to find the price of each product.

Excel Data

Search Table

Solution

In F2, enter:

=VLOOKUP(E2,$A$2:$D$6,4,FALSE)

Copy down.

Expected Output

Question 3: Use VLOOKUP with IFERROR

Problem Statement

Some Employee IDs may not exist in the employee table. Display "Employee Not Found" instead of an Excel error.

Excel Data

Search Table

Solution

In D2, enter:

=IFERROR(VLOOKUP(C2,$A$2:$C$6,2,FALSE),"Employee Not Found")

Copy down.

Expected Output

This is a practical example of combining VLOOKUP with IFERROR.

Question 4: Use HLOOKUP to Find Monthly Sales

Problem Statement

Monthly sales are arranged horizontally. Use HLOOKUP to find sales for a specified month.

Excel Data

Search Table

Solution

In B5, enter:

=HLOOKUP(A5,$B$1:$G$2,2,FALSE)

Copy down.

Expected Output

HLOOKUP searches across the first row and returns a value from a specified row.

Question 5: Find an Employee’s Salary Using XLOOKUP

Problem Statement

Use the Employee ID to return the employee’s salary.

Excel Data

Search Table

Solution

In F2, enter:

=XLOOKUP(E2,$A$2:$A$6,$D$2:$D$6)

Copy down.

Expected Output

Unlike VLOOKUP, XLOOKUP separately specifies the lookup range and return range.

Question 6: XLOOKUP with a Custom Not-Found Message

Problem Statement

Find the product price using Product ID. If the Product ID does not exist, display "Product Not Found".

Excel Data

Search Table

Solution

In E2, enter:

=XLOOKUP(D2,$A$2:$A$6,$C$2:$C$6,"Product Not Found")

Copy down.

Expected Output

The fourth argument of XLOOKUP specifies what should be displayed when no match is found.

Question 7: XLOOKUP to Return Multiple Columns

Problem Statement

Use one Employee ID to return the employee’s name, department, and salary.

Excel Data

Search Table

Solution

In B10, enter:

=XLOOKUP(A10,$A$2:$A$6,$B$2:$D$6)

Expected Output

In versions of Excel that support dynamic arrays, the formula can return multiple columns automatically.

Question 8: Find a Student’s Grade Using XLOOKUP

Problem Statement

Use the student’s roll number to find their grade.

Excel Data

Search Table

Solution

In E2, enter:

=XLOOKUP(D2,$A$2:$A$6,$D$2:$D$6)

Copy down.

Expected Output

Question 9: Approximate Lookup for Commission Rates

Problem Statement

A company gives different commission rates based on sales amount.

Find the appropriate commission rate for each salesperson.

Commission Table

Sales Data

Solution Using XLOOKUP

In C2, enter:

=XLOOKUP(B2,$E$2:$E$6,$F$2:$F$6,,-1)

Copy down.

Expected Output

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

Question 10: Build a Product Lookup Report

Problem Statement

Create a small product lookup report. Enter a Product ID and automatically return:

• Product Name
• Category
• Price
• Stock Quantity

Product Table

Search Report

Solution

In B9, enter:

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

The formula can return all four requested columns.

Expected Output

This is a useful real-world lookup pattern for inventory and product-reporting worksheets.

Key Takeaways

• VLOOKUP searches vertically in the first column of a table.
• HLOOKUP in Excel searches horizontally in the first row of a table.
• XLOOKUP can search vertically or horizontally and gives more flexibility.
• Use FALSE with VLOOKUP when you need an exact match.
• XLOOKUP in excel lets you specify the lookup range and return range separately.
• XLOOKUP can return multiple columns in supported Excel versions.
• IFERROR can be combined with VLOOKUP to handle missing records.
• XLOOKUP has a built-in argument for displaying a custom not-found message.
• Approximate lookup is useful for ranges such as commission rates, grades, discounts, and pricing slabs.
• Lookup functions are widely used in employee databases, inventory reports, sales reports, student records, and business dashboards.

FAQs

1. What is VLOOKUP used for in Excel?

VLOOKUP searches for a value in the first column of a table and returns related information from another column.

Example:

=VLOOKUP(A2,$A$2:$D$10,4,FALSE)

2. What is the difference between VLOOKUP and HLOOKUP?

VLOOKUP searches vertically down the first column.

HLOOKUP searches horizontally across the first row.

The choice depends on how your lookup table is arranged.

3. What is XLOOKUP in Excel?

XLOOKUP is a modern lookup function that searches one range and returns a corresponding value from another range.

Example:

=XLOOKUP(A2,$D$2:$D$10,$E$2:$E$10)

4. Why is FALSE used in VLOOKUP?

FALSE tells VLOOKUP to look for an exact match.

For example:

=VLOOKUP(A2,$A$2:$D$10,3,FALSE)

This is commonly used for IDs, product codes, employee IDs, and roll numbers.

5. How can I avoid #N/A when using VLOOKUP?

You can use IFERROR:

=IFERROR(VLOOKUP(A2,$A$2:$D$10,3,FALSE),"Not Found")

This replaces the error with your chosen message.

6. How can XLOOKUP display a message when a value is missing?

Use the fourth argument:

=XLOOKUP(A2,$D$2:$D$10,$E$2:$E$10,"Not Found")

If the lookup value doesn’t exist, Excel displays "Not Found".

7. Can XLOOKUP in excel return more than one column?

Yes. For example:

=XLOOKUP(A2,$A$2:$A$10,$B$2:$D$10)

In Excel versions supporting dynamic arrays, the result can spill into multiple columns.

8. When is HLOOKUP useful?

HLOOKUP is useful when the lookup values are arranged horizontally across the top row of a table, such as monthly sales or yearly figures.

9. Can VLOOKUP search from right to left?

Traditional VLOOKUP is designed to return values to the right of its lookup column. XLOOKUP does not have this restriction and can return values from a separate range regardless of its position.

10. Which lookup function should I practice first?

For learning lookup concepts, practice VLOOKUP first, then HLOOKUP, and finally XLOOKUP. The important skill is understanding the lookup value, lookup range, return range, exact matching, and handling missing data.

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

Rishabh Kumar