HLOOKUP Practice Exercises with Answers

In this article, you will get HLOOKUP exercises with answers for practice. When data is arranged row-wise this article will help you to get exact and approximate values, handle errors, get the highest value, extract multiple values, use wildcard characters for the approximate match, create an array of multiple returns, refer to another worksheet and extract multiple values in one cell, etc.

You can use HLOOKUP to extract specific information from vast datasets, such as sales records or employee performance reports. This skill is a game-changer for professionals, analysts, and students seeking to streamline data analysis and reporting.

You’ll practice different examples using HLOOKUP, MATCH, LARGE, and IFERROR functions. If you’re unsure about Excel tools, these exercises will teach you too.

No matter your Excel version – 2010, 2013, 2016, 2019, or 365 – these exercises are compatible except for one and it is Exercise 7. The array return method may require Excel 365.  Improve your data analysis skills by mastering HLOOKUP and related functions. Get ready to enhance your Excel skills in less time!

Overview of HLOOKUP Function in Excel

Function Objective:

The HLOOKUP function searches for a value in the top row of a table or array of values and returns the value in the same column from the specified row.

Syntax:

Hlookup Practice Exercises with Answers

The dataset contains information about employee sales for the months of April, May, June, and July. Each employee is identified by an Employee ID and Full Name, and their sales for each month are provided.

Exercise 01 Find Exact Match: What is the “Employee ID” for Employee “Jaime Schultz“? Use HLOOKUP to locate the employee’s name and get the corresponding Employee ID.

Exercise 02 Find Approximate Match: What are the sales of the month “Aprl” for Employee “Jaime Schultz“?

Hint: Notice that the spelling of the month of April is actually wrong.

Exercise 03 Get Sales Amount from ID: Find the sales for the month of “June” for the employee with Employee ID “ID–22006“.

Hint: Use the MATCH function to locate the row number of the employee id.

Exercise 04 Define Named Range for Table Array: What were the sales for the employee with Employee ID “ID-22004” in the month of “May” using this named range?

Hint: Defined a named range “EmployeeTable” for the range B4:G14.

Exercise 05 Wildcard Character for Approximate Match: What were the sales for the employee with a Full Name containing “Samantha Carson” in the month of “Jul“?.

Hint: Use Asterisk (*) wildcard for text/string values.

Exercise 06 Referring Another Worksheet: How to find the sales for the employee with Employee ID “ID-22003” for the month of “April” when data is in a different worksheet named “Sales“.

Exercise 07 Extract Array Values: Find the sales of the Month of April for Employee ID-22006, ID-22007, ID-22008, and ID-22009 as an array.

Hint: Curly bracket {} to create an array at the position of row values. This exercise may require an Excel 365 version

Exercise 08 Handle Error for Missing Values: Find the sales for the employee: “ID-22006” month of August. If the Month is not found, display “Month not found“.

Hint: Here, employ the IFERROR function to manage errors.

Exercise 09 Find Nth Highest Total Sales: Determine the “July” sales for the employee with the 2nd highest total sales.

Hint: The LARGE function to get the 2nd highest sales.

Exercise 10 Extract Multiple-Criteria: Extract the “Full Name,” “May” sales, and “July” sales for the employee with Employee ID “ID-22008“.

Conclusion

In these exercises, you’ll see how HLOOKUP makes hard tasks easy. It helps with sales data, student grades, and much more. You have get 10 easy to advanced level HLOOKUP practice exercise with answers in the article.  As you solve each problem, you’re learning skills that help in your job and studies. If you encounter any issues while solving the problems, rest assured that we’re here to help. Your success is our priority, and we’re committed to resolving any challenges you may face. Let’s learn and grow together as Excel enthusiasts. Happy problem-solving!