VLOOKUP, HLOOKUP, XLOOKUP and INDEX & MATCH all do the same basic job: given something you know, find something you don't, sitting elsewhere in the workbook. This page sets out how each one actually works, in plain English, with a worked formula for each.
BlackTor Group LtdDartmoor, UK
A note on this page
A reference, not a client write-up.
This page is a plain technical explainer, not a record of client work: no client names, no client data, nothing covered by an NDA. It sits alongside the main Excel page, and the Lists page on Data Validation dropdowns, as a deeper dive on the four lookup methods that come up most often.
The four methods
Same job, four different tools.
Each one searches for a value and returns something related to it. Where they differ is how they search, what they can search across, and how well they hold up once the workbook changes shape.
VLOOKUP: search down a column, read across
=VLOOKUP(B2,ProductTable,3,FALSE)
VLOOKUP looks for the value in B2 down the first column of ProductTable, then reads across to the 3rd column of that table and returns whatever it finds there. The final FALSE means an exact match only: without it, VLOOKUP will happily return the nearest match below the value it was actually looking for, which is rarely what's wanted.
The catch is that "3rd column" is just a number, with no idea what's actually in that column. Insert a new column into the middle of the table and every VLOOKUP built on it quietly starts reading the wrong field, with no error to flag it. VLOOKUP can also only search its own first column and return something to the right of it: it cannot look leftward.
HLOOKUP: the same idea, turned on its side
=HLOOKUP(B1,QuarterlyRates,5,FALSE)
HLOOKUP is VLOOKUP's horizontal counterpart: it searches along the first row of QuarterlyRates for the value in B1, then reads down to the 5th row of that range and returns what it finds there. It's the right tool specifically when a table's headers run across the columns rather than down the rows, for example a table of rates with each column being a different quarter.
Everything that makes VLOOKUP fragile applies here too, just rotated: insert a new row into the middle of the range and the "5th row" reference points at the wrong data, silently.
XLOOKUP looks for the value in B2 within the ProductCodes range, and returns the matching entry from ProductPrices, a separate range rather than a fixed column count within one table. That alone removes VLOOKUP's biggest weakness: since the two ranges are specified independently, inserting a column between them doesn't shift anything out of alignment.
Two further differences worth knowing: the 4th argument (here, "Not found") is what XLOOKUP returns when nothing matches, so there's no need to wrap the whole thing in IFERROR just to avoid an ugly #N/A. And the final 0 asks for an exact match; XLOOKUP also supports approximate matches and searching from the last item backwards, which is useful for "most recent entry" style lookups. The only real downside is availability: XLOOKUP needs a reasonably current version of Excel, so it isn't safe to use in a workbook that has to open correctly on an older install.
INDEX & MATCH: two steps instead of one
=INDEX(ProductPrices,MATCH(B2,ProductCodes,0))
INDEX and MATCH split the same job into two separate steps. MATCH finds the position of B2 within ProductCodes, as a plain number: first, second, third, and so on. INDEX then returns whatever sits at that position within ProductPrices, a completely separate range.
Because the position and the range being read are worked out independently, this combination doesn't break when a column is inserted or reordered, the same way XLOOKUP doesn't. It also works in either direction: MATCH doesn't care whether the range it's returning from sits to the left or right of the one it searched. Before XLOOKUP existed, this was the standard way to get around VLOOKUP's leftward-only, column-count limitations, and it's still worth knowing since it works in every version of Excel, including the ones too old for XLOOKUP.
Which one to use
A short rule of thumb.
If XLOOKUP is available, it's usually the best default: it's the most forgiving of the four when a workbook's layout changes later, and it needs no extra wrapping to handle a missing match cleanly. Where XLOOKUP isn't available, INDEX & MATCH gets the same resilience at the cost of one extra step to write. VLOOKUP and HLOOKUP are still perfectly reasonable for a small, stable table that isn't going to be restructured, and they remain the two most widely recognised by anyone else who opens the workbook.
