HLOOKUP Function

Summary

The Excel HLOOKUP function searches for a value in the top row of a table or array and returns a value from a specified row in the same column. It is the horizontal version of VLOOKUP, so it is most useful when your lookup keys are arranged across the top instead of down the first column.

In practice, HLOOKUP is often used in wide tables such as year-by-year models, monthly schedules, or horizontal product layouts. If the header row contains the value you want to find, HLOOKUP can retrieve the corresponding value from a lower row.

Although newer functions such as XLOOKUP are more flexible, HLOOKUP still appears in many existing workbooks, so it is worth understanding how it behaves and where its limits are.

Syntax

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

lookup_value is the item to find in the top row, table_array is the full table, row_index_num is the row to return from within that table, and range_lookup controls whether the match is exact or approximate.

The row index is counted relative to the selected table, not the worksheet. If your table starts in A1:C3, then row 1 means A1:C1, row 2 means A2:C2, and row 3 means A3:C3.

Arguments

• lookup_value - The value to search for in the first row of the table.
• table_array - The range containing both the header row and the rows that may be returned.
• row_index_num - The row number within the selected table to return from.
• range_lookup - [Optional] Use FALSE for exact match or TRUE for approximate match.

For approximate match, the top row should be sorted in ascending order. If it is not sorted, the returned result can be incorrect even though the formula does not show an error.

HLOOKUP vs Other Functions

HLOOKUP is one of several lookup functions, but its direction is its defining feature.

Use HLOOKUP when the table is already horizontal. If the workbook is being built from scratch, many users now prefer XLOOKUP for greater flexibility.

Using HLOOKUP

HLOOKUP works best in wide tables where the important labels are across the top. Typical examples include years in a forecast, months in a budget, or SKU codes in a horizontal specification table.

Exact match is the safer default for most text or code lookups. Approximate match is mainly for sorted numeric brackets such as thresholds or tier tables.

One limitation to keep in mind is that HLOOKUP uses a fixed row index. If rows are inserted inside the table, a hard-coded row number may point to the wrong result. Using MATCH to calculate the row index can make the formula more stable.

• Use FALSE for exact matches such as codes, labels, or specific years.
• Use TRUE only when the top row is sorted and you want bracket-style matching.
• Use MATCH for the row index if the table may gain or lose rows later.

Example 1 - Exact Match Yearly Rate Retrieval

This formula finds the year 2024 in the top row and returns the value from row 2 of the same column. It is a simple example of using HLOOKUP in a year-by-year table.

This is useful because it shows the basic shape HLOOKUP expects: labels across the top and return values underneath. When the table is horizontal like this, the formula reads naturally.

=HLOOKUP(2024, A1:C2, 2, FALSE)

Example 2 - Horizontal Spec Sheet Identification

Here HLOOKUP searches the top row for "SKU_2" and returns the material from row 3. This shows how the function can retrieve different attributes from the same matched column depending on the row index you provide.

That makes the example practical for wide product tables. The matched column stays the same, but the row index lets the sheet pull a different field such as material, price, or size.

=HLOOKUP("SKU_2", A1:C3, 3, FALSE)

Example 3 - Automated Quantity Discount Tiering

This formula uses approximate match to find the largest quantity breakpoint that does not exceed 150. Because the top row is sorted as 0, 100, 500, the lookup lands on 100 and returns the corresponding rate from row 2.

This helps explain why sorted order matters in approximate match mode. The formula is not looking for an exact 150 header, but for the correct bracket that 150 belongs to.

=HLOOKUP(150, A1:C2, 2, TRUE)

Example 4 - Relative Row-Safe Attribute Audit

Using MATCH inside the row index makes the return row dynamic. Instead of hard-coding row 3, the formula finds where "Metric_2" appears in column A and uses that position inside HLOOKUP.

This is useful for maintenance because the row position no longer has to be typed manually. If the table grows or shifts, the formula is less likely to point to the wrong row.

=HLOOKUP("Data_B", A1:C3, MATCH("Metric_2", A1:A3, 0), FALSE)

Conclusion Recap

HLOOKUP is the right fit when the labels you search are across the top row instead of down the first column. In this lesson, that showed up in year tables, spec sheets, discount tiers, and tables where the return row mattered too.

The two big things to remember are direction and match type. HLOOKUP always searches across the top, and FALSE is usually the safer choice unless you really want approximate matching in a sorted table.

• Summary: HLOOKUP searches the top row and returns a value from a lower row in the same column.
• Syntax: =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]).
• Core setup: Keep the lookup keys in the top row and remember the row index is relative to the selected table.
• Best use: Horizontal models such as year tables, month schedules, and wide specification sheets.