HLOOKUP

Look up a value in the top row of a table and return a value from the same column with HLOOKUP.

Lookup & Reference
|
Excel All versions
|
Google Sheets Supported

Spreadsheet editor

Syntax

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]) Returns: Varies

Arguments

Argument Required Description
lookup_value Yes The value to find in the top row of the table.
table_array Yes The range of cells that holds the data, with the values to search in the top row.
row_index_num Yes The row number inside table_array to return a value from. The top row is 1, the next row down is 2, and so on.
range_lookup No TRUE (or omitted) finds the closest match and needs the top row sorted ascending. FALSE finds an exact match.

About

HLOOKUP searches for a value across the top row of a table, then returns a value from the same column in a row you pick. The H stands for horizontal, so use it when your headers run left to right and your data sits below them, like months across the top with figures underneath.

Use HLOOKUP when you want to pull a figure that lines up with a label in the header row. Common cases include grabbing a month's total from a budget laid out across columns or finding a rate from a horizontal lookup table. By default HLOOKUP does an approximate match, which expects the top row sorted in ascending order, so add FALSE as the last argument when you need an exact match (most often the case).

Most data is laid out with headers down the left side instead, where VLOOKUP is the better fit. If your spreadsheet supports it, XLOOKUP handles both directions and is more forgiving, while INDEX and MATCH give you a flexible two-step alternative.

Examples

Find a commission rate from a tiered table

See how an approximate match works when thresholds run across the top row in ascending order. A sales amount of 12000 lands in the 10000 bracket and returns 5%. Change the amount and watch the rate shift.

Spreadsheet editor

Why you need FALSE for an exact match

Watch two formulas disagree when the top row isn't sorted. FALSE returns the right price, while TRUE quietly returns the wrong one. Edit the product name to see which lookups hold up.

Spreadsheet editor

Pick the row dynamically with MATCH

Combine HLOOKUP with MATCH to choose both the column and the row from labels. HLOOKUP finds the month while MATCH finds the metric, so the result updates when you change either one. Try switching the metric to Profit.

Spreadsheet editor

Watch out for

⚠

Forgetting FALSE for an exact match

Leaving off the last argument makes HLOOKUP do an approximate match. On unsorted data that quietly returns the wrong value instead of an error, which is easy to miss.

→ Add FALSE as the fourth argument whenever you need an exact match, like looking up a name, code, or label. Only use TRUE when your top row is sorted ascending and you want the closest match.

⚠

Counting row_index_num from the worksheet

row_index_num counts rows inside table_array, not the worksheet row numbers. If your table starts on row 5, asking for row 3 still means the third row of the range.

→ Count down from the top of your selected table: the header row is 1, the row below it is 2, and so on. A number larger than the table's height returns a #REF! error.

⚠

Using HLOOKUP on vertical data

HLOOKUP only searches the top row. If your labels run down a column instead of across a row, it cannot find them.

→ Switch to VLOOKUP for headers down the left side, or use XLOOKUP if you have it, since it works in any direction.

⚠

Approximate match on an unsorted top row

With range_lookup set to TRUE, HLOOKUP assumes the top row is sorted in ascending order. An unsorted row gives results that look random.

→ Sort the top row from smallest to largest before using TRUE, or switch to FALSE for an exact match where sort order does not matter.

Tips & notes

HLOOKUP can return either text or a number depending on what sits in the cell it lands on. In exact match mode (FALSE) it also accepts wildcards: use ? to match any single character and * to match any run of characters.

Common questions

What is the difference between HLOOKUP and VLOOKUP?

They work the same way but in different directions. HLOOKUP searches across the top row and returns a value from a row below, while VLOOKUP searches down the leftmost column and returns a value from a column to the right.

Why does HLOOKUP return #N/A?

An #N/A error means the lookup_value was not found in the top row. Check for typos, extra spaces, or a number stored as text, and make sure range_lookup is FALSE when you need an exact match.

Can HLOOKUP use wildcards?

Yes, as long as range_lookup is FALSE. Use ? to stand in for one character and * for any number of characters, for example "Q*" to match the first label starting with Q.

Should I still use HLOOKUP or switch to XLOOKUP?

HLOOKUP is fine and works in every version of Excel. If you have XLOOKUP in Excel 365 or 2021, it is easier to set up, returns an exact match by default, and looks up in any direction.