Excel HLOOKUP & LOOKUP — Horizontal Lookups and the Legacy Function to Retire

TL;DR — HLOOKUP is VLOOKUP turned on its side: it searches the first row left-to-right and returns a value from a row below — use it when your data runs across the top instead of down the side. Same fourth-argument rule: pass FALSE for exact match. LOOKUP is the grandparent of both, and it has one crippling limitation — no exact-match option at all. It always does an approximate match and requires the lookup range sorted ascending, which is why it silently returns wrong answers and why you should almost always replace it. Both work in every version; XLOOKUP handles horizontal and vertical in one function.

=HLOOKUP("Mar", A1:M4, 3, FALSE)     ' find "Mar" across row 1, return row 3, exact
=LOOKUP(F2, A2:A500, C2:C500)        ' legacy: approximate only, A must be sorted

VLOOKUP and INDEX/MATCH cover the vast majority of lookups. HLOOKUP and LOOKUP are the two remaining branches of the family — one you'll occasionally need, one you should mostly recognise and retire. Both make more sense once you see them as variations on the same scan-and-fetch idea, not new concepts to learn from scratch.

What you'll learn

• HLOOKUP as VLOOKUP rotated: it scans a row, then reads down
• When a horizontal layout actually makes HLOOKUP the right tool
• Why LOOKUP has no exact match — its one fatal design flaw
• The LOOKUP(2, 1/…) trick you'll find in old workbooks, and what it's doing
• Why XLOOKUP quietly makes both of these optional

HLOOKUP: VLOOKUP rotated 90 degrees

Everything you know about VLOOKUP applies to HLOOKUP — just swap "column" for "row." It searches the first row of the table from left to right, and when it finds your value it steps down by row_index_num rows:

=HLOOKUP(lookup_value, table, row_index_num, [range_lookup])
=HLOOKUP("Mar", A1:M4, 3, FALSE)   ' "Mar" is in row 1; return the 3rd row's cell

The H is for horizontal. The fourth argument behaves identically to VLOOKUP's: leave it off and you get an approximate match that needs the first row sorted left-to-right; pass FALSE for the exact match you almost always want. And like VLOOKUP, it only reads in one direction — downward from the match row — so the value you want must sit below the row you search.

When HLOOKUP is actually the right call

HLOOKUP earns its place when the data is genuinely laid out across the top: period labels (Jan, Feb, Mar…) running along row 1 with metrics stacked in the rows beneath. That's a transposed table, and for it HLOOKUP is the natural fit where VLOOKUP would need the data pivoted first.

' Row 1: Jan Feb Mar Apr ...   Row 2: Revenue   Row 3: Cost   Row 4: Margin
=HLOOKUP(G1, A1:M4, 4, FALSE)     ' G1 = "Mar" -> Margin for March

Be honest about how often that happens, though. Most Excel data is stored vertically — records down, fields across — which is why VLOOKUP and INDEX/MATCH dominate and HLOOKUP is a specialist. If you control the layout, vertical is almost always the better structure. HLOOKUP is for when you've inherited a horizontal one.

LOOKUP: the ancestor with one fatal flaw

LOOKUP predates VLOOKUP, and it has a design choice that disqualifies it from most modern use: it has no exact-match argument. There is no FALSE to pass. LOOKUP always does an approximate match and always assumes the lookup range is sorted ascending. Feed it unsorted data and it returns wrong answers with no error — the silent failure, with no way to turn it off.

Its more useful "vector form" takes three arguments — a lookup vector and a separate result vector:

=LOOKUP(F2, A2:A500, C2:C500)   ' search A (sorted!), return the matching C

That separation of lookup and result ranges is a nice property — it can look left, like INDEX/MATCH — but it's bought at the price of forced approximate matching on sorted data. Given the choice, an exact VLOOKUP, INDEX/MATCH, or XLOOKUP beats it in every dimension that matters.

The LOOKUP(2, 1/…) trick in old workbooks

You'll open an inherited sheet and find something cryptic like this:

=LOOKUP(2, 1/(A2:A500=F2), C2:C500)    ' returns the LAST match, not the first

It's a clever exploitation of LOOKUP's approximate nature. The expression 1/(A2:A500=F2) produces 1 where the row matches F2 and a #DIV/0! error where it doesn't. Searching for 2 — a value that never exists — forces LOOKUP to run off the end and settle on the last 1 it saw, i.e. the last matching row. It was the standard way to get a "last match" before modern tools existed.

You don't need to write it anymore, but you should recognise it. In Excel 365 the same intent is far clearer with XLOOKUP's reverse search:

=XLOOKUP(F2, A2:A500, C2:C500, , 0, -1)   ' -1 = search from the bottom up -> last match

The judgment call: where these two fit

Slot the whole family together and the decision tree is short:

• VLOOKUP — vertical data, exact match, return to the right. The everyday default.
• INDEX/MATCH — vertical data, but you need to look left, survive column edits, or do a two-way lookup.
• HLOOKUP — the data genuinely runs horizontally across the top.
• LOOKUP — you're reading someone else's old formula. For new work, replace it.
• XLOOKUP — on Excel 365, it does vertical, horizontal, left, right, last-match, and not-found defaults in one function, making HLOOKUP and LOOKUP largely optional.

Neither HLOOKUP nor LOOKUP is something to seek out. Know HLOOKUP for the transposed table you'll eventually meet, and know LOOKUP well enough to translate it into something better when you inherit it.

How ExcelMaster helps

Half the time these functions come up, it's because you've opened a workbook someone else built — a horizontal HLOOKUP report or a baffling LOOKUP(2,1/…) formula — and need to understand or modernise it. ExcelMaster reads the existing formula, explains what it's actually doing, and rewrites it into a clear XLOOKUP or INDEX/MATCH when the old approach is fragile — so you inherit the result, not the risk.

Frequently asked questions

What is the difference between HLOOKUP and VLOOKUP?

VLOOKUP searches vertically — down the first column — and returns a value from a column to the right. HLOOKUP searches horizontally — across the first row — and returns a value from a row below. Use HLOOKUP when your data is laid out with labels across the top instead of down the side. Both take the same fourth argument, where FALSE means exact match.

Why should I avoid the LOOKUP function?

LOOKUP has no exact-match option — it always does an approximate match and requires the data sorted ascending, so it silently returns wrong answers on unsorted data with no way to disable that behaviour. Use an exact VLOOKUP, INDEX/MATCH, or XLOOKUP instead, all of which support exact matching.

What does LOOKUP(2, 1/(range=value)) do?

It returns the last matching row. 1/(range=value) gives 1 for matching rows and errors elsewhere; searching for 2 (which never exists) makes LOOKUP settle on the last 1. It's a legacy "last match" trick — in Excel 365, use XLOOKUP(value, range, result, , 0, -1) for a clearer reverse search.

Does HLOOKUP have the same exact-match trap as VLOOKUP?

Yes. HLOOKUP's fourth argument (range_lookup) defaults to TRUE (approximate), which needs the first row sorted left-to-right. Pass FALSE for an exact match, the same as you would with VLOOKUP.

Can XLOOKUP replace HLOOKUP and LOOKUP?

Yes. On Excel 365, XLOOKUP searches in any direction — vertical or horizontal, forward or reverse — with exact match by default and a built-in not-found value, covering everything HLOOKUP and LOOKUP do and more.

Tested in

Tested in: Excel 365 (Windows 11) — last verified 2026-07-05.

Related guides: Excel VLOOKUP · Excel INDEX & MATCH · XLOOKUP (2025) · Excel IFERROR · Excel FILTER