HLOOKUP
HLOOKUP exists for one layout: a table whose labels run across a row instead of down a column. That is uncommon enough that the first thing worth checking is whether the data really is sideways — if the labels run downwards, this is a VLOOKUP question wearing the wrong name. When it genuinely is sideways, two things decide whether the formula keeps working. The row it counts to is a fixed number rather than a label, so inserting a row inside the table repoints it without complaint; and leaving the last argument off asks for an approximate match, which hands back a nearest-neighbour value rather than an error on an unsorted header row.
The formula
=HLOOKUP(A5, B1:M2, 2, FALSE) exact match: months in B1:M1, revenue in row 2 (B2:M2)
=HLOOKUP(A5, B1:M2, 2) range_lookup omitted -> approximate match, risky unless B1:M1 is sorted
=IFERROR(HLOOKUP(A5, B1:M2, 2, FALSE), "Not found") fall back to text instead of #N/AA worked example
Months run across B1:M1 as headers, revenue for each month sits in row 2 (B2:M2), so table_array is B1:M2; A5 holds the text "Mar".
=HLOOKUP(A5, B1:M2, 2, FALSE)
18500 — March's revenue. Omit FALSE and, if B1:M1 happens to already be in order, it can still return 18500 by accident; the mistake only shows up once the header row stops being sorted, not as a formula error.
Which one do I need?
| If you want to… | Use |
|---|---|
| Headers run across a row and the values you need sit in rows below (a sideways table) | HLOOKUP is the right shape: =HLOOKUP(lookup_value, table_array, row_index_num, FALSE) |
| Headers run down a column instead — the far more common layout | Use VLOOKUP, not HLOOKUP |
| You want one function that works in either direction and defaults to an exact match | XLOOKUP — Excel 365 and 2021+ only |
| You're on an older Excel without XLOOKUP, or the table's rows and columns get inserted or reordered | INDEX/MATCH — immune to a hardcoded row_index_num pointing at the wrong row |
Frequently asked questions
Why does HLOOKUP return the wrong value instead of an error?
range_lookup defaulted to an approximate match because it was left out, and the header row is not sorted in ascending order — approximate match then returns whatever value it lands nearest to, silently, instead of erroring. Add FALSE as the fourth argument to force an exact match.
What happens if the row I select doesn't start with my real header row?
HLOOKUP always searches row 1 of whatever range you give it as table_array — not necessarily your visual header. If you select a range that starts one row below your actual headers, HLOOKUP searches that first selected row instead, so it either returns #N/A or matches against the wrong row entirely. Include the header row as the first row of table_array.
When should I use XLOOKUP or INDEX/MATCH instead of HLOOKUP?
For anything beyond a small, unchanging sideways table: XLOOKUP (Excel 365/2021+) and INDEX/MATCH both re-locate the target row by its label every time, so inserting or reordering rows doesn't break them the way a hardcoded row_index_num in HLOOKUP does.
Does HLOOKUP work the same in Google Sheets?
Yes — HLOOKUP(search_key, range, index, [is_sorted]) takes the same four arguments in the same order. is_sorted defaults to TRUE (approximate match) just like Excel's range_lookup, so set it to FALSE for an exact match there too.
New guides and tools, once a month
DE + EN · double opt-in · no spam