The HLOOKUP function
VLOOKUP lying down: the value is looked for in the first row, and the answer taken further down, in the same column. For tables with months in columns.
Syntax
=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
In a French Excel: =RECHERCHEH(valeur_cherchée; table_matrice; no_index_lig; [valeur_proche]).
Arguments
- lookup_value: what to look for in the first row.
- table_array: the range, locked with
$. - row_index_num: the number of the row to return.
- range_lookup: FALSE for an exact lookup.
An example
=HLOOKUP("March",$B$1:$M$20,5,FALSE)
The value of the range's 5th row, in the "March" column.
Pitfalls
- Forgetting FALSE: approximate match, wrong result with no error.
- The hard-coded row number breaks when a row is inserted.
Going further
- VLOOKUP, XLOOKUP, which looks both ways.
- Unpivot or pivot: put the months back in rows, for simpler lookups.
Frequently asked questions
- HLOOKUP or XLOOKUP?
- XLOOKUP searches a row as well as a column, with no number to maintain, in Excel 2021 and Microsoft 365.