How to Use the HLOOKUP Function in Excel

The HLOOKUP function in Excel searches for a value across the top row of a table and returns related information from a row below. It is useful when your data is organized horizontally instead of vertically. Many businesses use HLOOKUP for product pricing tables, monthly sales comparisons, scorecards, and horizontally structured reports.

In This Guide, You’ll Learn

• What the HLOOKUP function does
• The syntax and arguments of HLOOKUP
• How to use HLOOKUP with beginner-friendly examples
• Common mistakes to avoid when using HLOOKUP
• Real-world business uses for HLOOKUP
• When to use HLOOKUP instead of VLOOKUP

What is the HLOOKUP Function?

The HLOOKUP function in Excel searches for a value in the first row of a table and returns a value from a specified row in the same column.

HLOOKUP Function Syntax

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

Argument Definitions

• lookup_value – The value you want to find in the first row of the table.
• table_array – The range containing the horizontal data table.
• row_index_num – The row number in the table from which Excel should return a result.
• [range_lookup] – Optional. Enter FALSE for an exact match or TRUE for an approximate match. Most users should use FALSE.

Example Dataset Used in This Tutorial

You can download the sample data we’ll be using in this tutorial by clicking on this link—> HLOOKUP Function Data

Basic HLOOKUP Formula

Imagine you have product names across the top row and product prices in the second row.

Formula Used

=HLOOKUP("Keyboard",A1:E2,2,FALSE)

Step-by-Step Explanation

• Excel searches the first row for Keyboard
• Once found, it moves down to row 2 of the selected range
• Excel returns the matching price: 75

Using a Cell Reference in HLOOKUP

Instead of typing the lookup value directly into the formula, reference a cell.

Formula Used

=HLOOKUP(G2,B1:E2,2,FALSE)

Step-by-Step Explanation

• Excel looks at cell G2 for the lookup value (Monitor)
• It searches the first row of the selected table
• It returns the value from row 2 in the same column

This approach is better because users can change the lookup value without editing the formula.

Finding Employee Performance Scores

HLOOKUP is useful for horizontally organized employee performance data.

Formula Used

=HLOOKUP("Sarah",A1:E2,2,FALSE)

Step-by-Step Explanation

• Excel searches the first row for Sarah
• It finds Sarah’s column
• Excel returns the value from row 2 (95)

Using Approximate Match with HLOOKUP

Sometimes you want Excel to return approximate values.

Formula Used

=HLOOKUP(6200,B1:E2,2,TRUE)

Step-by-Step Explanation

• Excel searches for 6200 in the first row
• Since 6200 is not found exactly, Excel finds the closest value without going over (5000)
• It returns the corresponding commission rate (6%)

Important: For approximate matches, the first row must be sorted from smallest to largest.

Common HLOOKUP Mistakes

Forgetting FALSE for Exact Matches

If you leave out FALSE, Excel may return unexpected results.

Incorrect:

=HLOOKUP("Keyboard",A1:E2,2)

Better:

=HLOOKUP("Keyboard",A1:E2,2,FALSE)

Choosing the Wrong Row Number

If your table has two rows but you request row 3, Excel returns an error.

Searching in Vertical Data

HLOOKUP only works with horizontal tables. If your data runs vertically, use the Excel function VLOOKUP instead.

Incorrect Table Range

Make sure the selected table range includes all rows needed for the result.

Example Dataset Used in This Tutorial

You can download the sample data we’ll be using in this tutorial by clicking on this link—> HLOOKUP Function Data

Related Articles