HLOOKUP Generator
Search across the first row of a table and return a value from a row below it.
HLOOKUP
Search across the first row of a table and return a value from a row below it.
Cell or value to find in the first row of the table range.
Range whose FIRST ROW contains the values being searched.
Row number counted down from the first row of the table range. Row 1 is the header row itself.
FALSE for an exact match. TRUE needs the header row sorted in ascending order, left to right.
=HLOOKUP(A2, $B$1:$M$5, 3, FALSE)Worked Example
Month names sit across row 1 in B1:M1 and revenue sits in row 3. A2 holds the month you want.
=HLOOKUP(A2, $B$1:$M$5, 3, FALSE)Returns: With A2 = "Mar", Excel finds Mar in the header row (column D) and returns the value in D3 - the third row of the table range.
Checks Before You Paste
- •HLOOKUP only searches the FIRST ROW of the table range, and row_index_num counts down from that same row - so 1 returns the header you just matched, and 2 is the first row of data.
- •Leaving the last argument out (or setting TRUE) switches to approximate match, which requires the header row sorted in ascending order left to right and quietly returns a value from the wrong column when it is not. Use FALSE unless you deliberately want banded lookup.
- •HLOOKUP can never look upward: the return row must sit inside the table range, at or below the header row. If your answer is above the matched row, use INDEX/MATCH or XLOOKUP instead.