When should I use HLOOKUP instead of VLOOKUP?

Use HLOOKUP when your lookup keys run across the top row (months, scenarios, regions as columns) and the values you want sit in rows below. Use VLOOKUP when keys run down the first column. They are transposes of each other.

What does row_index_num mean?

It's the row within table_array to return, counted from 1 at the top (key) row. row_index_num 2 returns the second row of the range. If it exceeds the range's row count you get #REF!.

Why does HLOOKUP return #REF!?

Almost always because row_index_num is outside 1..(number of rows in table_array) — too big, zero, or negative. Count the rows in the range and keep the index inside that span.

How do I force an exact match?

Set the fourth argument to FALSE (or 0). Omitting it defaults to TRUE (approximate), which requires the top row sorted ascending and silently returns wrong columns on unsorted headers.

Is HLOOKUP faster than VLOOKUP or XLOOKUP?

HLOOKUP shares VLOOKUP's lookup engine and speed class — it's the horizontal transpose, not a faster algorithm. Choose it for data orientation, not speed; for flexible or left/up lookups prefer XLOOKUP or INDEX-MATCH.

HLOOKUP — Syntax, Horizontal Lookups, 14 Error Fixes & Compatibility

fx HLOOKUP

=HLOOKUP(lookup_value, table_array, row_index_num, [range_lookup])

lookup_valuerequired

The value to find in the top row of the table.

table_arrayrequired

The horizontal range; keys must be in its first row.

row_index_numrequired

Which row to return (1 = the key row). Must fit the range's height.

range_lookupoptional

FALSE/0 = exact (use this). TRUE/1 = approximate, needs sorted headers.

// 02 — Original data HLOOKUP vs VLOOKUP vs XLOOKUP

We benchmarked exact-match lookups in HyperFormula 3.3.1 — median recalculation time across four table sizes. This is original measured data, not republished.

VLOOKUP XLOOKUP INDEX-MATCH

Table rows	VLOOKUP (ms)	XLOOKUP (ms)	INDEX-MATCH (ms)

How we measured · who made this

LookupLab Research · run Nov 2026 · Apple Silicon · HyperFormula 3.3.1 · median of repeated runs

Exact-match lookups on a sorted table, full-column reference, median recalculation time across four table sizes. Every result was correctness-verified before timing. This is directly relevant to web spreadsheet tools and indicative, not identical to native Excel. HLOOKUP is VLOOKUP's transpose — same lookup engine, same speed class, not a faster algorithm. Our benchmark measures vertical exact-match lookups; pick HLOOKUP when your keys run across the top row, and VLOOKUP / INDEX-MATCH when they run down a column. Orientation, not speed, is the deciding factor.

⬇ Reproduction script (bench.mjs) ⬇ Raw data (bench-hf.json)

Text drafted with AI assistance and edited by LookupLab. Every benchmark number is machine-measured and reproducible, not generated. Data licensed CC BY 4.0.

// 03 — Try it live

Interactive lookup playground

Edit the cells, change the lookup key, switch functions and match modes. The result and the generated formula update instantly.

A1Employee data. Click any cell to edit

Lookup key (ID) 1003

Return column

Function

Match mode

Table rows 8

Grid theme

Highlight match

IFERROR wrap

Fallback value

Result

$62,000

=VLOOKUP(1003,A2:D9,4,0)

✓ foundinstant

// 04 — Error library

The real-world error library

14 failure patterns mined from actual user reports on Reddit, Stack Overflow and Super User — each with the authentic symptom, root cause, a fix, and a runnable formula. Not textbook theory.

🔍

// 05 — Cross-platform

Where VLOOKUP works

Availability and syntax differences across desktop Excel, Google Sheets, LibreOffice and the web spreadsheet engines — including the exact-match quirk we found in our benchmark.

Platform	Support	Notes	Gotcha

✓ Supported full VLOOKUP◐ Partial works with caveatsCompiled from public docs · verify against your build

// 06 — Go deeper

🔒

Unlock the full VLOOKUP course

Everything above is free. Go Pro for the deep end.

✓ 200 graded practice problems with instant feedback ✓ INDEX-MATCH vs VLOOKUP deep dive + when to switch ✓ Two-way & leftward lookup patterns ✓ Downloadable workbook + completion certificate

$9/mo $59/yr · save 45% $149 lifetime

Keys across the top?
HLOOKUP reads rows, not columns.

Interactive lookup playground

The real-world error library

Where VLOOKUP works

Unlock the full VLOOKUP course

Frequently asked

Interactive lookup playground

The real-world error library

Where VLOOKUP works

Unlock the full VLOOKUP course

Frequently asked

Related functions