VLOOKUP vs HLOOKUP: vertical or horizontal?
They're the same function rotated 90°: VLOOKUP searches the first COLUMN and reads across; HLOOKUP searches the first ROW and reads down. VLOOKUP dominates because data almost always grows downward — but when you meet a horizontal table, HLOOKUP (or better, XLOOKUP) is the answer.
The differences at a glance
| VLOOKUP | HLOOKUP | |
|---|---|---|
| Searches | First column of the range | First row of the range |
| Returns from | A column N to the right | A row N below |
| Typical data shape | Records as rows (normal tables) | Time periods or attributes as columns |
| Same pitfalls | Approximate-match default, positional index | Identical — pass FALSE, count rows carefully |
| Modern replacement | XLOOKUP (any direction) | XLOOKUP works sideways too — one function replaces both |
| Compatibility | Universal | Universal (every version of all three apps we test) |
Which should you use?
- VLOOKUP — Normal record-per-row tables — the 95% case.
- HLOOKUP — Header-per-column layouts like months across the top (budget sheets, quarter columns) that you can't restructure. If you can, TRANSPOSE the data or use XLOOKUP instead.
Compatibility (from executed tests)
Both execute correctly in every version of Excel, Google Sheets, and LibreOffice we test — they're part of the ancient core. XLOOKUP, which replaces both directions, needs Excel 2021+/365, current Sheets, or LibreOffice 24.8+ (verified: #NAME? in LO 24.2).
Example formulas
| VLOOKUP (data in rows) | =VLOOKUP("cherry",A2:B10,2,FALSE) |
| HLOOKUP (months across the top) | =HLOOKUP("Mar",B1:M5,3,FALSE) |
| XLOOKUP (replaces both) | =XLOOKUP("Mar",B1:M1,B3:M3) |
Full per-version details on each function page: VLOOKUP · HLOOKUP.