HLOOKUP
Search the first row of a range and return a value from further down the same column.
Syntax
HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])| Argument | Required | Description |
|---|---|---|
lookup_value | Required | The value to search for, always in the first row of table_array. |
table_array | Required | The range to search. Its top row is the one that gets searched. |
row_index_num | Required | Which row of the range to return, counted from 1. |
range_lookup | Optional | FALSE for exact match, TRUE or omitted for approximate. Pass FALSE. |
Examples
Every example below was executed against VisiGrid engine 0.31.0 — not
transcribed from another vendor's documentation. Reproduce any of them with vgrid calc.
| Formula | Result | Notes |
|---|---|---|
=HLOOKUP("beta",A1:C2,2,FALSE) | 102 | Headers across row 1, values in row 2. |
=HLOOKUP("bet*",A1:C2,2,FALSE) | 102 | Wildcards work in an exact-match lookup. |
=HLOOKUP("zz",A1:C2,2,FALSE) | #N/A | No matching column. |
=HLOOKUP("102",A2:B2,1,FALSE) | #N/A | Same rule as VLOOKUP — types must agree. |
VLOOKUP turned on its side
HLOOKUP is VLOOKUP with rows and columns swapped: it searches the first
row of the range and returns from a row further down, rather than searching
the first column and returning from a column further right.
Everything true of VLOOKUP is true here. The fourth argument still defaults to
approximate, so still pass FALSE. The row index is still a position, so
inserting a row inside the range still changes what it points at without
erroring.
It is rarer for a reason
Most spreadsheet data grows downward — new rows for new records — so the value
you look up is usually in a column. HLOOKUP fits the shape where periods run
across the top and metrics down the side, which is common in reporting layouts
and rare in data tables.
If you find yourself reaching for it often, the data may be pivoted the wrong way for the rest of your formulas.
Excel compatibility
Matches Excel, including wildcards and the approximate-match default. A text lookup value never matches a numeric cell, and a numeric one never matches a text cell — in either direction and whatever the spelling, as in Excel.