INDEX MATCH
What it's for
INDEX/MATCH does the same basic job as VLOOKUP — finding one value based on another — but it's built from two smaller formulas working together instead of one all-in-one function. That makes it a little more to read at first, but also more flexible.
When you'd use it
Reach for INDEX/MATCH when VLOOKUP's biggest limitation gets in your way: needing to look up a value that sits to the left of the column you want to search. VLOOKUP can't do that at all. INDEX/MATCH doesn't care which direction your columns are in.
How it works
It helps to think of INDEX and MATCH as two separate jobs combined into one formula. MATCH's job is to find a position, not an answer — given a value to search for and a range to search in, MATCH tells you something like "that value is the 14th item in this list," just a number representing a location. INDEX's job is to fetch whatever sits at a specific position in a range — on its own, INDEX doesn't search for anything, you have to tell it exactly which position you want. Put together, MATCH finds where something is, and INDEX goes and gets what's there.
A worked example
=INDEX(A:A, MATCH("Maria Chen", B:B, 0))Department names are in column A, employee names in column B — the department is to the left of what you're looking up, which VLOOKUP can't handle. MATCH finds which row "Maria Chen" is on in column B. INDEX then goes to that same row number, but in column A, and returns whatever's there.
A tip worth knowing
The 0 inside MATCH means "exact match only," the same role FALSE plays inside VLOOKUP. It's easy to forget since it's just a lone number sitting at the end, but leaving it out changes how the formula behaves in ways that are hard to notice until something looks wrong.
Have a formula of your own you're stuck on?
Paste it and get an explanation →