Introduction
The HLOOKUP function helps you search for a value in the first row of a table and retrieve related information from another row. It is useful when your data is arranged horizontally, with headings or lookup values across columns. In this tutorial, you will learn HLOOKUP from the basics with practical examples.
What Is the HLOOKUP Function in Excel?
HLOOKUP stands for Horizontal Lookup.
It searches for a value in the first row of a selected table and returns a value from a specified row in the same column.
For example, suppose a company maintains monthly sales data horizontally, with months in the first row and sales amounts in the rows below.
If you want to retrieve the sales amount for February, HLOOKUP can search for February in the first row and return the corresponding sales figure.
In simple words:
- Excel searches for the required value in the first row of the selected table.
- It identifies the column containing that value.
- It moves down to the row number you specify.
- It returns the value from that position.
HLOOKUP Function Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
Understanding the Arguments
| Argument | Meaning |
|---|---|
| lookup_value | The value you want Excel to search for in the first row. |
| table_array | The complete table or range containing the lookup value and result rows. |
| row_index_num | The row number from which Excel should return the result. |
| range_lookup | Specifies whether you want an exact or approximate match. |
Understanding Each Argument
1. lookup_value
This is the value you want to find in the first row of your selected table.
For example, if you want to find February’s sales, February is your lookup_value.
2. table_array
This is the complete range containing your lookup data.
For example, if your table is in A1:F4, Excel searches for the lookup value in the first row of this selected range.
3. row_index_num
This argument tells Excel which row contains the result you want.
Suppose your selected table contains the following rows:
| Row Position | Information |
|---|---|
| 1 | Month |
| 2 | Laptop Sales |
| 3 | Mobile Sales |
| 4 | Tablet Sales |
If you want to retrieve Mobile Sales, use row number 3.
Important: HLOOKUP counts rows from the beginning of the selected table, not from the worksheet’s actual row numbers.
4. range_lookup
This argument controls the type of matching.
| Value | Meaning |
|---|---|
| FALSE or 0 | Finds an exact match. |
| TRUE or 1 | Finds an approximate match. |
For most employee, product, and monthly sales lookups, use FALSE because you generally want an exact match.
If you omit this argument, Excel uses approximate matching by default.
How Does the HLOOKUP Function Work?
Suppose a company maintains the following monthly sales records:
| Sales Details | January | February | March | April |
|---|---|---|---|---|
| Laptop Sales | 50000 | 60000 | 70000 | 80000 |
| Mobile Sales | 30000 | 35000 | 40000 | 45000 |
| Tablet Sales | 20000 | 25000 | 30000 | 35000 |
The sales manager wants to retrieve Laptop Sales for February.
Use this formula:
=HLOOKUP("February",A1:E4,2,FALSE)
Let’s understand how Excel evaluates the formula:
"February"is the month Excel needs to find.A1:E4is the selected sales table.2tells Excel to return the value from the second row of the selected table.FALSEtells Excel to find an exact match.- Excel finds February in the second column and returns the value from the second row.
Expected Result: 60000
The result is 60000 because February’s Laptop Sales amount is 60000.
Practical Examples of HLOOKUP Function in Excel
Let’s practice HLOOKUP using different real-world situations.
Question 1: Find Monthly Sales
Problem Statement
A company maintains monthly sales figures in a horizontal table. The sales manager wants to retrieve the sales amount for March.
Data Table
| Sales Details | January | February | March | April | May |
|---|---|---|---|---|---|
| Total Sales | 25000 | 30000 | 45000 | 40000 | 55000 |
Excel Formula
=HLOOKUP("March",A1:F2,2,FALSE)
Expected Result
| Month | Sales |
|---|---|
| March | 45000 |
Explanation
"March"is the month Excel needs to find.A1:F2contains the monthly sales data.2selects the second row, which contains Total Sales.FALSEsearches for an exact month name.- Excel returns 45000.
Concept Covered
Retrieving monthly sales from a horizontally arranged table using an exact match.
Question 2: Find an Employee’s Salary
Problem Statement
An HR department maintains employee information horizontally. The manager wants to retrieve Priya’s salary.
Data Table
| Employee Details | E101 | E102 | E103 | E104 | E105 |
|---|---|---|---|---|---|
| Employee Name | Rahul | Priya | Aman | Neha | Karan |
| Department | IT | HR | Sales | Finance | IT |
| Salary | 35000 | 40000 | 45000 | 50000 | 55000 |
Excel Formula
=HLOOKUP("E102",A1:F4,4,FALSE)
Expected Result
| Employee ID | Salary |
|---|---|
| E102 | 40000 |
Explanation
"E102"is the Employee ID to search for.A1:F4is the employee table.4selects the fourth row, which contains Salary.FALSEsearches for an exact Employee ID.- Excel returns 40000.
Concept Covered
Retrieving employee information from a horizontally arranged employee table.
Question 3: Find a Product’s Price
Problem Statement
An electronics store maintains product information horizontally. The manager wants to find the price of the Monitor.
Data Table
| Product Details | Laptop | Keyboard | Mouse | Monitor | Printer |
|---|---|---|---|---|---|
| Product ID | P101 | P102 | P103 | P104 | P105 |
| Category | Electronics | Accessories | Accessories | Electronics | Electronics |
| Price | 55000 | 1200 | 600 | 12000 | 8500 |
Excel Formula
=HLOOKUP("Monitor",A1:F4,4,FALSE)
Expected Result
| Product | Price |
|---|---|
| Monitor | 12000 |
Explanation
"Monitor"is the product name to search for.A1:F4contains the product information.4selects the fourth row, which contains Price.FALSEsearches for an exact product name.- Excel returns 12000.
Concept Covered
Retrieving product prices from a horizontally arranged product table.
Question 4: Find a Student’s Subject Marks
Problem Statement
A school maintains student marks horizontally. The teacher wants to retrieve the Mathematics marks of student Riya.
Data Table
| Student Details | Aarav | Riya | Mohit | Simran | Kabir |
|---|---|---|---|---|---|
| Roll Number | 101 | 102 | 103 | 104 | 105 |
| Mathematics | 85 | 92 | 75 | 89 | 80 |
| English | 78 | 88 | 82 | 91 | 76 |
| Science | 90 | 95 | 80 | 87 | 84 |
Excel Formula
=HLOOKUP("Riya",A1:F5,3,FALSE)
Expected Result
| Student | Mathematics Marks |
|---|---|
| Riya | 92 |
Explanation
"Riya"is the student name Excel needs to find.A1:F5contains the student records.3selects the third row, which contains Mathematics marks.FALSEsearches for an exact student name.- Excel returns 92.
Concept Covered
Retrieving subject marks from a horizontally arranged student table.
Question 5: Understand Approximate Match in HLOOKUP
Problem Statement
A company provides incentives based on employee sales. The incentive percentage depends on the sales amount.
The manager wants to find the applicable incentive for an employee who has achieved sales of 35000.
Data Table
| Minimum Sales | 0 | 10000 | 20000 | 30000 | 40000 |
|---|---|---|---|---|---|
| Incentive | 2% | 3% | 5% | 7% | 10% |
Excel Formula
=HLOOKUP(35000,A1:F2,2,TRUE)
Expected Result
| Sales Amount | Incentive |
|---|---|
| 35000 | 7% |
Explanation
35000is the sales amount to search for.A1:F2contains the sales thresholds and incentive percentages.2selects the second row, which contains Incentive.TRUEtells Excel to use approximate matching.- Excel finds the largest sales threshold that is less than or equal to 35000. That value is 30000, so the result is 7%.
Concept Covered
Using approximate matching to find the applicable incentive from a horizontally arranged threshold table.
Important: When using approximate matching with TRUE, the first row of the selected table must be sorted in ascending order.
Question 6: Find a Department Using a Cell Reference
Problem Statement
A company maintains employee information horizontally. The manager wants to enter an Employee ID in a separate cell and retrieve the employee’s department.
Data Table
| Employee Details | E201 | E202 | E203 | E204 | E205 |
|---|---|---|---|---|---|
| Employee Name | Arjun | Meera | Dev | Pooja | Rohan |
| Department | IT | HR | Sales | Finance | Marketing |
| City | Delhi | Noida | Gurgaon | Delhi | Noida |
Assume cell H2 contains E203.
Excel Formula
=HLOOKUP(H2,A1:F4,3,FALSE)
Expected Result
| Employee ID | Department |
|---|---|
| E203 | Sales |
Explanation
H2contains the Employee ID to search for.A1:F4is the employee table.3selects the third row, which contains Department.FALSEsearches for an exact Employee ID.- Excel returns Sales.
Concept Covered
Using a cell reference as the lookup value. You can change the Employee ID in H2 to retrieve information for another employee.
Question 7: Retrieve a Value from a Fixed Table Range
Problem Statement
A company maintains quarterly sales records horizontally. The manager wants to retrieve the sales amount for Quarter 3 and keep the lookup table range fixed when copying the formula.
Data Table
| Sales Details | Q1 | Q2 | Q3 | Q4 |
|---|---|---|---|---|
| North Region | 50000 | 60000 | 70000 | 80000 |
| South Region | 45000 | 55000 | 65000 | 75000 |
| East Region | 40000 | 50000 | 60000 | 70000 |
| West Region | 55000 | 65000 | 75000 | 85000 |
Assume cell G2 contains Q3.
Excel Formula
=HLOOKUP(G2,$A$1:$E$5,2,FALSE)
Expected Result
| Quarter | North Region Sales |
|---|---|
| Q3 | 70000 |
Explanation
G2contains the quarter to search for.$A$1:$E$5is the selected sales table.- The dollar signs make the table range absolute, so it remains fixed when copying the formula.
2selects the second row, which contains North Region sales.FALSEsearches for an exact quarter.- Excel returns 70000.
Concept Covered
Using an absolute table reference in HLOOKUP to keep the selected range fixed when copying a formula.
Student Practice Tasks
Try these tasks yourself using the HLOOKUP function.
Task 1: Find Monthly Revenue
| Revenue Details | January | February | March | April |
|---|---|---|---|---|
| Revenue | 20000 | 25000 | 30000 | 35000 |
Retrieve the revenue for April.
Task 2: Find an Employee’s Department
| Employee Details | E301 | E302 | E303 | E304 |
|---|---|---|---|---|
| Employee Name | Amit | Rahul | Neha | Priya |
| Department | IT | HR | Sales | Finance |
| Salary | 30000 | 35000 | 40000 | 45000 |
Retrieve the department of employee E303.
Task 3: Find Student Marks
| Student Details | Ankit | Neha | Rahul | Simran |
|---|---|---|---|---|
| Roll Number | 201 | 202 | 203 | 204 |
| English | 75 | 88 | 70 | 95 |
| Maths | 80 | 92 | 78 | 89 |
| Science | 85 | 90 | 82 | 93 |
Retrieve the Science marks of Neha.
Task 4: Find an Incentive Percentage
| Minimum Sales | 0 | 10000 | 20000 | 30000 | 40000 |
|---|---|---|---|---|---|
| Incentive | 2% | 3% | 5% | 7% | 10% |
Find the incentive percentage for sales of 25000 using approximate matching.
Final Student Challenge: Build a Monthly Sales Finder
Create the following sales table in Excel.
| Sales Details | January | February | March | April | May | June |
|---|---|---|---|---|---|---|
| Laptop Sales | 50000 | 60000 | 70000 | 80000 | 90000 | 95000 |
| Mobile Sales | 30000 | 35000 | 40000 | 45000 | 50000 | 55000 |
| Tablet Sales | 20000 | 25000 | 30000 | 35000 | 40000 | 45000 |
| Printer Sales | 10000 | 12000 | 15000 | 18000 | 20000 | 22000 |
Create a separate search area:
| Search Field | Value |
|---|---|
| Month | March |
| Laptop Sales | |
| Mobile Sales | |
| Tablet Sales | |
| Printer Sales |
Your Tasks
- Retrieve Laptop Sales for the selected month.
- Retrieve Mobile Sales for the selected month.
- Retrieve Tablet Sales for the selected month.
- Retrieve Printer Sales for the selected month.
- Change the month in the search area and check whether all sales figures update correctly.
Challenge Goal: Build a simple monthly sales finder using HLOOKUP and cell references. This exercise will help you understand how horizontally arranged data can be used in business reports.
Key Takeaways
- HLOOKUP stands for Horizontal Lookup.
- It searches for a value in the first row of the selected table.
- It returns information from another row in the same column.
lookup_valuespecifies what you want to find.table_arraydefines the lookup table.row_index_numidentifies the row from which Excel should return the result.FALSEis used for exact matching.TRUEis used for approximate matching and requires ascending-sorted lookup data in the first row.- HLOOKUP is useful when data is arranged horizontally.
- Unlike VLOOKUP, HLOOKUP searches across columns and returns information from rows.
Frequently Asked Questions (FAQs)
1. What is the HLOOKUP function in Excel?
HLOOKUP is an Excel function that searches for a value in the first row of a selected table and returns a related value from another row in the same column.
2. What does HLOOKUP stand for?
HLOOKUP stands for Horizontal Lookup because it searches horizontally across the first row of a selected table.
3. What is the syntax of HLOOKUP?
The syntax is:
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
4. What is the difference between TRUE and FALSE in HLOOKUP?
FALSE searches for an exact match, while TRUE searches for an approximate match. Approximate matching requires the first row of the selected table to be sorted in ascending order.
5. What does row_index_num mean in HLOOKUP?
It specifies the position of the row from which Excel should return the result. The counting starts from the first row of the selected table.
6. Why is the first row important in HLOOKUP?
HLOOKUP always searches for the lookup value in the first row of the selected table. If the lookup value is not in that row, HLOOKUP cannot directly find it.
7. Can HLOOKUP search using a cell reference?
Yes. You can enter the lookup value in a cell and use that cell reference as the first argument. This makes it easier to change the search value.
8. What happens if HLOOKUP cannot find a value?
If Excel cannot find the required value using the specified matching method, HLOOKUP returns the #N/A error.
9. What is the difference between VLOOKUP and HLOOKUP?
VLOOKUP searches for a value in the first column of a table and returns information from another column. HLOOKUP searches for a value in the first row and returns information from another row.
10. When should we use HLOOKUP in Excel?
Use HLOOKUP when your lookup values are arranged horizontally across the first row of a table and you need to retrieve related information from rows below them.

