How to Use HLOOKUP in Excel: Syntax, Example, and Tips

Use the HLOOKUP function in Excel to search for a value in the first row of a table and return a value from a specified row in the same column. The syntax is HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]), where you specify what to find, where to look, which row to return, and whether to find an exact or approximate match.

Syntax

The HLOOKUP function has four parts:

• lookup_value: The value you want to find in the first row.
• table_array: The range of cells where you want to search.
• row_index_num: The row number in the table from which to return the value (1 is the first row).
• range_lookup (optional): TRUE for approximate match (default), FALSE for exact match.

excel

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

Example

This example shows how to find the sales for "Mar" from a horizontal table.

excel

A1: Month  | B1: Jan | C1: Feb | D1: Mar | E1: Apr
A2: Sales  | B2: 100 | C2: 150 | D2: 200 | E2: 175

Formula in A4: =HLOOKUP("Mar", A1:E2, 2, FALSE)

Output

200

Common Pitfalls

Common mistakes when using HLOOKUP include:

• Not setting range_lookup to FALSE when you need an exact match, which can cause wrong results.
• Using a row_index_num that is less than 1 or greater than the number of rows in the table.
• Looking up a value that is not in the first row of the table.

excel

Wrong: =HLOOKUP("Mar", A1:E2, 2)  // range_lookup defaults to TRUE, may return wrong result
Right: =HLOOKUP("Mar", A1:E2, 2, FALSE)  // exact match ensures correct result

Quick Reference

Parameter	Description
lookup_value	Value to find in the first row
table_array	Range of cells to search
row_index_num	Row number to return value from
range_lookup	TRUE for approximate, FALSE for exact match (optional)

Key Takeaways

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

Always set range_lookup to FALSE for exact matches to avoid errors.

row_index_num must be within the table's row count to avoid errors.

HLOOKUP only looks in the first row of the table_array for the lookup_value.