How to Use VLOOKUP and HLOOKUP in Excel: Real-World Applications

Knowing how VLOOKUP works is one thing; knowing which lookup to reach for in a real situation is another. Real data isn’t always laid out conveniently — sometimes the key runs across the top instead of down the side, sometimes you need to match a row and a column, sometimes the value you want sits to the left of your key. This covers the real-world jobs lookups handle, VLOOKUP’s horizontal twin HLOOKUP, and the two-way lookups that VLOOKUP alone can’t manage.

The everyday job: merge two tables

The most common real use is pulling a value from a reference table into your working data — an order list that needs each product’s price from a separate price sheet.

=VLOOKUP(A2, Products!$A:$C, 3, FALSE)

Each order row looks up its product code in the Products sheet and returns the price from the third column. This is VLOOKUP at its best: a clean reference table, a key in the leftmost column, and a value to its right. Keep your reference data — prices, rates, customer details — on its own tidy sheet, and a single lookup brings any field into your working data wherever you need it. The VLOOKUP fundamentals (always FALSE, lock the table) apply to every example here. The real payoff is maintenance: change a price once on the Products sheet, and every order that looks it up updates automatically. You never touch the order rows — the lookup keeps them in sync with the single source of truth. That’s the difference between a spreadsheet you maintain in one place and one where a price change means hunting through hundreds of rows.

HLOOKUP: when your data runs sideways

Sometimes data is laid out horizontally — months across the top row, values beneath. VLOOKUP can’t search a row, but HLOOKUP can: it’s the exact same function rotated 90 degrees.

=HLOOKUP("Mar", A1:M5, 3, FALSE)

This searches the top row for “Mar” and returns the value three rows down in that column. The arguments mirror VLOOKUP exactly — lookup value, table, row index, exact-match flag — just searching the first row instead of the first column. Use HLOOKUP whenever your categories are column headers rather than row labels, which happens often with time-series data formatted for human reading — a budget with months running across, a schedule with days as columns. Microsoft’s guide to lookup functions covers both directions. In practice, the cleanest move when you control the layout is to avoid needing HLOOKUP at all — restructure the data so categories run down a column, where VLOOKUP (and every other tool) works more naturally. Reach for HLOOKUP when you’re stuck with someone else’s horizontal layout you can’t change.

Two-way lookup: match a row and a column

Real tables are often grids — population by country (rows) and year (columns), or price by product and region. To pull one cell, you need to match both dimensions, which neither VLOOKUP nor HLOOKUP does alone.

=INDEX(B2:M10, MATCH("France", A2:A10, 0), MATCH(2026, B1:M1, 0))

This is the INDEX/MATCH combination: the first MATCH finds which row “France” is in, the second finds which column 2026 is in, and INDEX returns the cell where they cross. It’s the cleanest way to do a matrix lookup, and it’s why INDEX/MATCH is worth learning beyond VLOOKUP — it looks in any direction and matches on both axes at once. For a quick two-way lookup without INDEX, you can also nest MATCH inside VLOOKUP’s column argument to make the returned column dynamic: =VLOOKUP("France", A:M, MATCH(2026, A1:M1, 0), FALSE) finds the right column by year rather than hard-coding it. This is genuinely useful in reports where the column you need shifts — instead of editing the index number every quarter, MATCH finds the right column by its header, so the formula keeps working as columns are added. It’s the kind of small robustness that separates a formula you rewrite constantly from one you set and forget.

Approximate match for tiers and grades

Not every lookup wants an exact match. For banded data — commission tiers, tax brackets, letter grades — you want “which range does this fall into,” which is approximate match (TRUE).

=VLOOKUP(B2, Brackets!$A$2:$B$6, 2, TRUE)

With a bracket table sorted ascending by threshold, this returns the band a value falls into — a score of 82 lands in the “B” row, a sale of $12,000 lands in the 5% commission tier. The one rule that makes it work: the lookup column must be sorted ascending, or approximate match returns nonsense. It’s the legitimate use of TRUE that the exact-match advice doesn’t cover, and it turns a lookup into a classifier. The real-world cases are everywhere once you spot the pattern: shipping cost by weight band, discount by order quantity, performance rating by score, tax owed by income bracket. Each is a table of thresholds and outcomes, and approximate-match VLOOKUP maps any input value to the right band in one formula — far cleaner than a stack of nested IF statements that you’d have to rewrite every time a threshold changes.

Build a quick lookup tool

Combine a lookup with a dropdown and you’ve built a mini search interface — pick a product, see all its details appear at once. It’s one of the most useful applied patterns.

This turns a flat reference table into something you query by clicking. A warehouse worker picks a code and sees the item; a salesperson picks a customer and sees their terms — no scrolling, no filtering, just select and read. It’s the same VLOOKUP you’d write anywhere, anchored to one input cell so several lookups share it. Add it to a dashboard and you’ve given non-spreadsheet users a search box they can actually use, built from nothing but a dropdown and a few lookups.

Which lookup, when

The honest summary: if you’re on a recent Excel, XLOOKUP replaces all of these — it looks any direction, handles horizontal lookups by referencing rows, and returns exact matches by default, making HLOOKUP and even INDEX/MATCH largely unnecessary for new work. But VLOOKUP and HLOOKUP are everywhere in existing spreadsheets, so you’ll need to read and maintain them regardless, and INDEX/MATCH remains the most portable choice that works in every version. Match the function to the data’s layout and your version of Excel, and any lookup a real table throws at you has a clean answer. The deciding question is always the same: where is my key, and where is the value relative to it? Answer that, and the right function — vertical, horizontal, or two-way — picks itself.