One editable table, two formulas side by side. HLOOKUP searches a horizontal header row and returns a value down a row index; VLOOKUP searches a vertical key column and returns a value across a column index. Same exact/approximate match logic, rotated 90°. Every result below is evaluated live in HyperFormula 3.5.
HLOOKUP and VLOOKUP are the same function rotated 90°: HLOOKUP matches a header in the top row and returns down a row index; VLOOKUP matches a key in the left column and returns across a column index. Both support exact (FALSE) and approximate (TRUE) match, both return #N/A on a missing key and #REF! on an out-of-range index, and both time identically in our engine. Because data is usually stored vertically, VLOOKUP is the default; reach for HLOOKUP only for wide, short cross-tab layouts.
Every ✓ / ◐ / ✗ is a behavior we evaluated in HyperFormula 3.5 — not a marketing claim.
| Capability | HLOOKUP | VLOOKUP | Why it matters |
|---|---|---|---|
| Search axis | ✓ Top row (horizontal) | ✓ Left column (vertical) | The ONLY structural difference. |
| Index argument | ◐ row_index_num (down) | ◐ col_index_num (across) | Both 1-based, both #REF! if out of range. |
| Exact match | ✓ FALSE | ✓ FALSE | Identical behavior on the matching axis. |
| Approximate match | ✓ TRUE, top row ascending | ✓ TRUE, left col ascending | Same largest-value-≤-key algorithm. |
| Missing key | ◐ #N/A | ◐ #N/A | Wrap either in IFERROR/IFNA. |
| Index out of range | ◐ #REF! | ◐ #REF! | row 9 / col 9 in a 4-row / 5-col table. |
| Duplicate keys | ✓ First match wins | ✓ First match wins | Neither warns about duplicates. |
| Speed (HyperFormula) | ✓ ~35ms / 200 lookups @300 | ✓ ~34ms / 200 lookups @300 | Fair square-table test — essentially tied. |
Identical argument shape; the index counts rows for HLOOKUP and columns for VLOOKUP.
Both formulas run on the SAME editable matrix (Metric down column A, quarters across row 1). HLOOKUP keys on the quarters; VLOOKUP keys on the metrics. Edit a cell and watch live.
These are not feature-list claims. Each pair below was evaluated on the identical dataset in the HyperFormula 3.5 engine, and the outputs are baked from that run. Reproduce them in the playground above.
=HLOOKUP("Q3",A1:E4,4,FALSE)=VLOOKUP("Profit",A1:E4,4,FALSE)=HLOOKUP("Q1",A1:E4,2,FALSE)=VLOOKUP("Q1",A1:E4,2,FALSE)=HLOOKUP("Q3",A1:D4,2,FALSE)=VLOOKUP("Q3",A1:D4,4,FALSE)=HLOOKUP(75,A1:F2,2,TRUE)=VLOOKUP(75,A1:F2,2,TRUE)=HLOOKUP("Q9",A1:E4,4,FALSE)=VLOOKUP("Margin",A1:E4,4,FALSE)=HLOOKUP("Q3",A1:E4,9,FALSE)=VLOOKUP("Profit",A1:E4,9,FALSE)=HLOOKUP("Q1",A1:E3,2,FALSE)=VLOOKUP("Revenue",A1:E3,2,FALSE)200 lookups per engine on SQUARE tables (100 / 200 / 300 keys): HLOOKUP scans a 2-row horizontal slice, VLOOKUP a 2-column vertical slice — equal cell counts, a fair orientation test. Near-identical in HyperFormula 3.5 (35ms vs 34ms at 300). Orientation is a data-shape decision, not a speed decision. HyperFormula is NOT Excel — relative signal only.
| Table size | HLOOKUP | VLOOKUP |
|---|---|---|
| 100 rows × 200 lookups | 20.93 ms | 17.95 ms |
| 200 rows × 200 lookups | 24.44 ms | 23.41 ms |
| 300 rows × 200 lookups | 34.94 ms | 34.28 ms |
Let the table's orientation pick the function — vertical keys → VLOOKUP, horizontal headers → HLOOKUP.
| Situation | Use | Why |
|---|---|---|
| Keys run down the first column | VLOOKUP | VLOOKUP searches a vertical key column — the usual table layout. |
| Headers run across the top row | HLOOKUP | HLOOKUP searches a horizontal header row and returns down a row index. |
| Exact match on a key | Either | Both use FALSE / 0 for exact match and behave identically. |
| Approximate match on sorted thresholds | Either | Both use TRUE / 1 and require the lookup row/column sorted ascending. |
| A wide, short cross-tab report | HLOOKUP | HLOOKUP reads across a header row without transposing the layout. |
| A tall record list (one row per item) | VLOOKUP | VLOOKUP reads down a key column — the natural shape for records. |
| You need to return a whole row or column | Either | Neither returns an array alone — pair with INDEX or use XLOOKUP/FILTER. |
| You want one function for both orientations | Either | XLOOKUP searches any row or column with a built-in default — see the related page. |
Transpose the table so keys run down a column, then swap the row index for a column index.
| Task | HLOOKUP / old way | VLOOKUP | What changed |
|---|---|---|---|
| Exact lookup | =HLOOKUP("Q3",A1:E4,4,FALSE) | =VLOOKUP("Revenue",A2:E4,3,FALSE) | Same exact-match logic; transpose so keys run down a column for VLOOKUP. |
| Row vs column index | =HLOOKUP("Q1",A1:E4,3,FALSE) | =VLOOKUP("Revenue",A2:E4,3,FALSE) | HLOOKUP's 3 is a row number; VLOOKUP's 3 is a column number. |
| Missing key | =HLOOKUP("Q9",A1:E4,2,FALSE) | =VLOOKUP("Missing",A2:E4,2,FALSE) | Both return #N/A on a missing key; wrap in IFNA. |
| Index out of range | =HLOOKUP("Q1",A1:E4,9,FALSE) | =VLOOKUP("Revenue",A2:E4,9,FALSE) | Both return #REF! when the index exceeds the table. |
| Guard the #N/A | =IFNA(HLOOKUP("Q9",A1:E4,2,FALSE),"none") | =IFNA(VLOOKUP("Missing",A2:E4,2,FALSE),"none") | IFNA catches the not-found error on either side. |