Function comparison · engine-measured

HLOOKUP vs VLOOKUP

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.

fx
HLOOKUP(…)  vs  VLOOKUP(…)
7
measured scenarios
row↔col
the only real difference
1:1
identical engine timing
FALSE
exact-match default
▶ Try both live See the feature matrix
🏆Verdict: VLOOKUP for vertical tables; HLOOKUP only when headers run across a row.

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.

HLOOKUP strengths

  • Reads a horizontal header row naturally
  • Row index returns a value below the header
  • Same exact/approximate match modes as VLOOKUP
  • Handy for wide, short cross-tab layouts

VLOOKUP strengths

  • Matches how data is usually stored — vertically
  • Column index returns a field to the right
  • The default lookup most users already know
  • Approximate match for sorted vertical tiers

Feature-by-feature matrix

Every ✓ / ◐ / ✗ is a behavior we evaluated in HyperFormula 3.5 — not a marketing claim.

CapabilityHLOOKUPVLOOKUPWhy 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✓ FALSEIdentical behavior on the matching axis.
Approximate match✓ TRUE, top row ascending✓ TRUE, left col ascendingSame largest-value-≤-key algorithm.
Missing key◐ #N/A◐ #N/AWrap 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 winsNeither warns about duplicates.
Speed (HyperFormula)✓ ~35ms / 200 lookups @300✓ ~34ms / 200 lookups @300Fair square-table test — essentially tied.

Syntax, side by side

Identical argument shape; the index counts rows for HLOOKUP and columns for VLOOKUP.

HLOOKUP

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])
lookup_valueThe value to find in the TOP row of table_array.
table_arrayThe horizontal table, e.g. A1:E4 (headers across row 1).
row_index_numWhich ROW to return (1 = the header row itself).
range_lookupFALSE = exact; TRUE/omitted = approximate (top row ascending).
=HLOOKUP("Q3",A1:E4,4,FALSE)

VLOOKUP

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
lookup_valueThe value to find in the LEFTMOST column of table_array.
table_arrayThe vertical table, e.g. A1:E4 (keys down column A).
col_index_numWhich COLUMN to return (1 = the lookup column itself).
range_lookupFALSE = exact; TRUE/omitted = approximate (left column ascending).
=VLOOKUP("Revenue",A2:E4,3,FALSE)

Live head-to-head playground

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.

A1Sample data — click any cell to edit; both formulas re-evaluate live

HLOOKUP

—

VLOOKUP

—
HyperFormula 3.5 · live

Every difference, measured

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.

Same cell, two directions — the tie

Reach Profit x Q3 (600): HLOOKUP searches the quarter across row 1; VLOOKUP searches the metric down column A.
HLOOKUP
=HLOOKUP("Q3",A1:E4,4,FALSE)
600
VLOOKUP
=VLOOKUP("Profit",A1:E4,4,FALSE)
600
Both return 600 (Profit × Q3). HLOOKUP finds 'Q3' across row 1 and drops to row 4; VLOOKUP finds 'Profit' down column A and moves to column 4. Same cell, two directions.

The key lives in the TOP row

Look up the quarter key 'Q1' and return the Revenue row.
HLOOKUP
=HLOOKUP("Q1",A1:E4,2,FALSE)
1200
VLOOKUP
=VLOOKUP("Q1",A1:E4,2,FALSE)
#N/A
HLOOKUP returns 1200 (Revenue × Q1); VLOOKUP returns #N/A. The key 'Q1' lives in the TOP row, which only HLOOKUP searches — VLOOKUP looks down column A (Metric names) and never finds a quarter there.

Transposed data — the mirror image

The same numbers stored vertically (quarters down column A). Now the quarter key only works for VLOOKUP.
HLOOKUP
=HLOOKUP("Q3",A1:D4,2,FALSE)
#N/A
VLOOKUP
=VLOOKUP("Q3",A1:D4,4,FALSE)
600
The mirror image: with the data transposed (quarters down column A), HLOOKUP returns #N/A and VLOOKUP returns 600. Now the quarter key is in the left column, which only VLOOKUP searches. Match the function to the data's shape — or TRANSPOSE.

Approximate match — same algorithm, own axis

Horizontal discount bands (0/10/50/100/500 across row 1). Find the band for 75.
HLOOKUP
=HLOOKUP(75,A1:F2,2,TRUE)
0.1
VLOOKUP
=VLOOKUP(75,A1:F2,2,TRUE)
#N/A
HLOOKUP returns 0.1 (the 50 band, largest ≤ 75); VLOOKUP returns #N/A. Both run the same approximate-match algorithm requiring an ascending sort, but each searches its own axis — on this horizontal band table only HLOOKUP can find the numeric key.

Missing key — both #N/A

HLOOKUP a quarter that does not exist; VLOOKUP a metric that does not exist.
HLOOKUP
=HLOOKUP("Q9",A1:E4,4,FALSE)
#N/A
VLOOKUP
=VLOOKUP("Margin",A1:E4,4,FALSE)
#N/A
Both return #N/A. A key that does not exist on the searched axis fails identically in both functions; wrap either in IFERROR or IFNA for a clean default.

Index out of range — both #REF!

Ask for row/column 9 in a table that only has 4 rows / 5 columns.
HLOOKUP
=HLOOKUP("Q3",A1:E4,9,FALSE)
#REF!
VLOOKUP
=VLOOKUP("Profit",A1:E4,9,FALSE)
#REF!
Both return #REF!. Asking for row 9 in a 4-row table (HLOOKUP) or column 9 in a 5-column table (VLOOKUP) exceeds the bounds — the index argument is 1-based and out-of-range in both.

Duplicate headers — first match wins

Row 1 has Q1 twice (columns B and D). Return the Revenue row for 'Q1'.
HLOOKUP
=HLOOKUP("Q1",A1:E3,2,FALSE)
100
VLOOKUP
=VLOOKUP("Revenue",A1:E3,2,FALSE)
100
Both return 100. Row 1 has 'Q1' twice (columns B and D); HLOOKUP stops at the first. VLOOKUP on 'Revenue' returns column 2, the first Q1 column. Neither function warns about duplicate keys — first match wins in both.

Engine benchmark

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 sizeHLOOKUPVLOOKUP
100 rows × 200 lookups20.93 ms17.95 ms
200 rows × 200 lookups24.44 ms23.41 ms
300 rows × 200 lookups34.94 ms34.28 ms
Measured in the HyperFormula 3.5 engine on this page, not in Microsoft Excel. Absolute times are engine-specific; use only as a relative signal. Real Excel performance depends on version, data layout and calculation chain.

Which one should you use?

Let the table's orientation pick the function — vertical keys → VLOOKUP, horizontal headers → HLOOKUP.

SituationUseWhy
Keys run down the first columnVLOOKUPVLOOKUP searches a vertical key column — the usual table layout.
Headers run across the top rowHLOOKUPHLOOKUP searches a horizontal header row and returns down a row index.
Exact match on a keyEitherBoth use FALSE / 0 for exact match and behave identically.
Approximate match on sorted thresholdsEitherBoth use TRUE / 1 and require the lookup row/column sorted ascending.
A wide, short cross-tab reportHLOOKUPHLOOKUP reads across a header row without transposing the layout.
A tall record list (one row per item)VLOOKUPVLOOKUP reads down a key column — the natural shape for records.
You need to return a whole row or columnEitherNeither returns an array alone — pair with INDEX or use XLOOKUP/FILTER.
You want one function for both orientationsEitherXLOOKUP searches any row or column with a built-in default — see the related page.

HLOOKUP → VLOOKUP migration

Transpose the table so keys run down a column, then swap the row index for a column index.

TaskHLOOKUP / old wayVLOOKUPWhat 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.

Frequently asked questions

What is the difference between HLOOKUP and VLOOKUP?
Orientation. HLOOKUP (horizontal) searches the TOP row of the table and returns a value from a specified row below it. VLOOKUP (vertical) searches the LEFTMOST column and returns a value from a specified column to its right. The syntax, match modes, and error behavior are otherwise identical — they are the same function rotated 90 degrees.
When should I use HLOOKUP?
When your lookup keys are arranged horizontally across a header row — e.g. months or quarters across the top and metrics down the side, and you want to find a column by its header then pull a row. If the keys run down the left column instead, use VLOOKUP. Most real-world tables are vertical, which is why VLOOKUP is far more common.
Can VLOOKUP do a horizontal lookup?
Not directly — it only searches the leftmost column. To use VLOOKUP on horizontal data, transpose the table first (TRANSPOSE function or paste-special transpose) so the keys move into the left column. Alternatively use HLOOKUP, or XLOOKUP in Excel 2021+/365, which searches a row or a column with the same syntax.
Do HLOOKUP and VLOOKUP need sorted data for approximate match?
Yes — both require the searched axis (top row for HLOOKUP, left column for VLOOKUP) sorted ASCENDING when range_lookup is TRUE or omitted. They return the largest value less than or equal to the key. Unsorted data gives silently wrong answers. For exact match (FALSE) no sorting is needed.
Which is faster, HLOOKUP or VLOOKUP?
In our fair square-table benchmark they are essentially tied (35ms vs 34ms for 200 lookups at 300 keys in HyperFormula). Speed should not drive the choice — orientation does. If you want one function that handles both axes without an index number, XLOOKUP (Excel 2021+/365) or INDEX/MATCH is the modern answer.