How to Use HLOOKUP in Excel

HLOOKUP searches for a value in the top row of a table and returns a value from a row you specify in the same column. The H stands for horizontal, so it scans across columns rather than down rows. It suits data laid out with labels in the first row and periods running left to right, which is common in financial summaries.

Syntax and how HLOOKUP works

The signature is =HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup]). The function looks for lookup_value in the first row of table_array, then moves down that matching column by row_index_num rows and returns the value it finds.

The row_index_num is counted from the top of the table, where the header row itself is row 1. So row_index_num of 3 returns the value two rows below the matched header. The fourth argument controls match type: FALSE forces an exact match, while TRUE or an omitted argument does an approximate match that assumes the top row is sorted ascending.

Worked example: pull a metric by quarter

Quarters sit across the top in B1:E1 as Q1 through Q4. Revenue is in row 2, gross profit in row 3, and net income in row 4. You want Q3 net income.

1. Confirm the header row holds the quarter labels: B1:E1 contains Q1, Q2, Q3, Q4.
2. Net income is the fourth row of the block, so row_index_num is 4 counting from row 1.
3. Enter =HLOOKUP("Q3", B1:E4, 4, FALSE).
4. Excel finds Q3 in column D, drops to the fourth row, and returns 1500.

Q3 is the matched column and net income is row 4 of the table, so the result is 1500.

How analysts use HLOOKUP in models

HLOOKUP fits time series that run across the page, which is the standard layout for forecast and actuals blocks.

• Pull a single period figure from a wide schedule into a summary cell.
• Drive a scenario picker by matching a chosen period label in a header row.
• Always pass FALSE for the fourth argument when labels are text, so a near match never returns the wrong period.
• Reference the lookup value from an input cell rather than typing a literal, so the formula follows your selection.

Common pitfalls

HLOOKUP is fragile because row_index_num is a hardcoded position. Insert a row inside the table and the count no longer points where you intended, so the formula silently returns the wrong line. It also only looks in the first row of the range, so the metric you search on must sit at the very top.

For most work, VLOOKUP or XLOOKUP is safer because financial data is usually arranged in columns and XLOOKUP returns a value from a named return range rather than a positional offset. Reach for HLOOKUP only when the data is genuinely horizontal and you cannot transpose it.