HLOOKUP Function in Excel Tutorial

Excel Tutorial

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:

  1. Excel searches for the required value in the first row of the selected table.
  2. It identifies the column containing that value.
  3. It moves down to the row number you specify.
  4. It returns the value from that position.

HLOOKUP Function Syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

Understanding the Arguments

ArgumentMeaning
lookup_valueThe value you want Excel to search for in the first row.
table_arrayThe complete table or range containing the lookup value and result rows.
row_index_numThe row number from which Excel should return the result.
range_lookupSpecifies 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 PositionInformation
1Month
2Laptop Sales
3Mobile Sales
4Tablet 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.

ValueMeaning
FALSE or 0Finds an exact match.
TRUE or 1Finds 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 DetailsJanuaryFebruaryMarchApril
Laptop Sales50000600007000080000
Mobile Sales30000350004000045000
Tablet Sales20000250003000035000

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:

  1. "February" is the month Excel needs to find.
  2. A1:E4 is the selected sales table.
  3. 2 tells Excel to return the value from the second row of the selected table.
  4. FALSE tells Excel to find an exact match.
  5. 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 DetailsJanuaryFebruaryMarchAprilMay
Total Sales2500030000450004000055000

Excel Formula

=HLOOKUP("March",A1:F2,2,FALSE)

Expected Result

MonthSales
March45000

Explanation

  • "March" is the month Excel needs to find.
  • A1:F2 contains the monthly sales data.
  • 2 selects the second row, which contains Total Sales.
  • FALSE searches 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 DetailsE101E102E103E104E105
Employee NameRahulPriyaAmanNehaKaran
DepartmentITHRSalesFinanceIT
Salary3500040000450005000055000

Excel Formula

=HLOOKUP("E102",A1:F4,4,FALSE)

Expected Result

Employee IDSalary
E10240000

Explanation

  • "E102" is the Employee ID to search for.
  • A1:F4 is the employee table.
  • 4 selects the fourth row, which contains Salary.
  • FALSE searches 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 DetailsLaptopKeyboardMouseMonitorPrinter
Product IDP101P102P103P104P105
CategoryElectronicsAccessoriesAccessoriesElectronicsElectronics
Price550001200600120008500

Excel Formula

=HLOOKUP("Monitor",A1:F4,4,FALSE)

Expected Result

ProductPrice
Monitor12000

Explanation

  • "Monitor" is the product name to search for.
  • A1:F4 contains the product information.
  • 4 selects the fourth row, which contains Price.
  • FALSE searches 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 DetailsAaravRiyaMohitSimranKabir
Roll Number101102103104105
Mathematics8592758980
English7888829176
Science9095808784

Excel Formula

=HLOOKUP("Riya",A1:F5,3,FALSE)

Expected Result

StudentMathematics Marks
Riya92

Explanation

  • "Riya" is the student name Excel needs to find.
  • A1:F5 contains the student records.
  • 3 selects the third row, which contains Mathematics marks.
  • FALSE searches 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 Sales010000200003000040000
Incentive2%3%5%7%10%

Excel Formula

=HLOOKUP(35000,A1:F2,2,TRUE)

Expected Result

Sales AmountIncentive
350007%

Explanation

  • 35000 is the sales amount to search for.
  • A1:F2 contains the sales thresholds and incentive percentages.
  • 2 selects the second row, which contains Incentive.
  • TRUE tells 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 DetailsE201E202E203E204E205
Employee NameArjunMeeraDevPoojaRohan
DepartmentITHRSalesFinanceMarketing
CityDelhiNoidaGurgaonDelhiNoida

Assume cell H2 contains E203.

Excel Formula

=HLOOKUP(H2,A1:F4,3,FALSE)

Expected Result

Employee IDDepartment
E203Sales

Explanation

  • H2 contains the Employee ID to search for.
  • A1:F4 is the employee table.
  • 3 selects the third row, which contains Department.
  • FALSE searches 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 DetailsQ1Q2Q3Q4
North Region50000600007000080000
South Region45000550006500075000
East Region40000500006000070000
West Region55000650007500085000

Assume cell G2 contains Q3.

Excel Formula

=HLOOKUP(G2,$A$1:$E$5,2,FALSE)

Expected Result

QuarterNorth Region Sales
Q370000

Explanation

  • G2 contains the quarter to search for.
  • $A$1:$E$5 is the selected sales table.
  • The dollar signs make the table range absolute, so it remains fixed when copying the formula.
  • 2 selects the second row, which contains North Region sales.
  • FALSE searches 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 DetailsJanuaryFebruaryMarchApril
Revenue20000250003000035000

Retrieve the revenue for April.

Task 2: Find an Employee’s Department

Employee DetailsE301E302E303E304
Employee NameAmitRahulNehaPriya
DepartmentITHRSalesFinance
Salary30000350004000045000

Retrieve the department of employee E303.

Task 3: Find Student Marks

Student DetailsAnkitNehaRahulSimran
Roll Number201202203204
English75887095
Maths80927889
Science85908293

Retrieve the Science marks of Neha.

Task 4: Find an Incentive Percentage

Minimum Sales010000200003000040000
Incentive2%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 DetailsJanuaryFebruaryMarchAprilMayJune
Laptop Sales500006000070000800009000095000
Mobile Sales300003500040000450005000055000
Tablet Sales200002500030000350004000045000
Printer Sales100001200015000180002000022000

Create a separate search area:

Search FieldValue
MonthMarch
Laptop Sales
Mobile Sales
Tablet Sales
Printer Sales

Your Tasks

  1. Retrieve Laptop Sales for the selected month.
  2. Retrieve Mobile Sales for the selected month.
  3. Retrieve Tablet Sales for the selected month.
  4. Retrieve Printer Sales for the selected month.
  5. 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_value specifies what you want to find.
  • table_array defines the lookup table.
  • row_index_num identifies the row from which Excel should return the result.
  • FALSE is used for exact matching.
  • TRUE is 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.