INDEX + MATCH: The Flexible Lookup
INDEX picks a value by its position and MATCH finds that position — together they look up data in any direction, even to the left.
What you will learn
- Use INDEX to grab a value by row number
- Use MATCH to find which row holds a value
- Combine them into a lookup that beats VLOOKUP
- Build a two-way (grid) lookup by row and column
Why learn another lookup?
You already know VLOOKUP and XLOOKUP. So why learn INDEX + MATCH? Two reasons. First, it can look to the left of the search column, which VLOOKUP simply cannot do. Second, you will meet it constantly in real workplace spreadsheets and older files, so an analyst is expected to read and write it. Think of it as the lookup that works in every direction.
It is really two small functions working as a team. Let us meet each one on its own first, then snap them together.
INDEX: give me the value at this position
INDEX answers a simple question: “in this range, what value sits at position number N?” You give it a range and a row number, and it hands back whatever is there. It does not search — it just counts to the position you name.
Take this price list in column B:
| A | B | |
|---|---|---|
| 1 | Product | Price |
| 2 | Apples | 50 |
| 3 | Bananas | 30 |
| 4 | Cherries | 90 |
=INDEX(B2:B4, 2)Note: Output: 30 INDEX counted down the range B2:B4 to position 2 (which is cell B3) and returned what it found there: the price 30. It did not search for anything — you told it the position and it fetched the value.
MATCH: tell me which position holds this value
MATCH does the opposite job: “in this range, at what position does this value sit?” You give it a value to find and a range, plus a 0 at the end which means exact match. It returns a position number, not the value itself.
=MATCH("Bananas", A2:A4, 0)Note: Output: 2 MATCH looked down A2:A4 and found Bananas in the 2nd spot, so it returned 2. Notice it gives a position number, not the price — that number is exactly what INDEX needs.
Watch out: Always end MATCH with 0 for an exact match, just like you end VLOOKUP with FALSE. Without the 0, MATCH does an approximate match on sorted data and can return the wrong position.
Snap them together
Here is the trick: MATCH produces a position number, and INDEX needs a position number. So feed MATCH into INDEX. Read it inside-out — MATCH finds the row, INDEX grabs the value from that row:
=INDEX(B2:B4, MATCH("Bananas", A2:A4, 0))Note: Output: 30 Step by step: MATCH("Bananas", A2:A4, 0) worked out to 2. The formula then became INDEX(B2:B4, 2), which returned the price 30. You looked up a product and got its price — the same result as VLOOKUP, but built from two clear pieces.
The whole idea in three steps
- MATCH searches the column you know (here, Product) and returns a row position.
- INDEX takes that position and reaches into the column you want (here, Price).
- Because the two ranges are separate, the Price column can be anywhere — even to the left of Product.
The superpower: look to the left
Imagine the Price is in column A and the Product is in column B — the reverse of before. VLOOKUP is stuck, because it can only return columns to the right of the search column. INDEX + MATCH does not care about direction at all:
=INDEX(A2:A4, MATCH("Bananas", B2:B4, 0))Note: Output: 30 MATCH found Bananas in column B (position 2), and INDEX pulled position 2 from column A — to the left. VLOOKUP could never do this; this left-lookup is the classic reason analysts reach for INDEX + MATCH.
The two-way lookup: find a value by row AND column
There is one more trick INDEX + MATCH does beautifully, and ordinary lookups cannot: a two-way lookup, also called a grid lookup. This is when you need the value sitting where a particular row meets a particular column — like reading a price off a grid by choosing both a product (the row) and a size (the column).
The idea is simple once you see it: INDEX can take two position numbers — a row number and a column number — and return the value where they cross. So you use one MATCH to find the row and a second MATCH to find the column, then hand both to INDEX. Here is a small price grid where the prices sit in B2:D4, products run down column A, and sizes run across row 1:
| A | B | C | D | |
|---|---|---|---|---|
| 1 | Product | Small | Medium | Large |
| 2 | Apples | 20 | 35 | 50 |
| 3 | Bananas | 15 | 25 | 40 |
| 4 | Cherries | 30 | 45 | 70 |
Say you want the price of a Bananas in a Large size. You ask: which row is Bananas, and which column is Large? Two MATCH functions answer those, and INDEX reads the crossing point:
=INDEX(B2:D4, MATCH("Bananas", A2:A4, 0), MATCH("Large", B1:D1, 0))Note: Output: 40 Step by step: the first MATCH found Bananas in A2:A4 at position 2 (the 2nd row of the grid). The second MATCH found Large in B1:D1 at position 3 (the 3rd column of the grid). The formula then became INDEX(B2:D4, 2, 3), which returned the value where row 2 meets column 3 — the price 40. Change either word and the formula reads a different cell of the grid, so one formula can price any product in any size.
Watch out: In a two-way lookup, INDEX takes the row position first, then the column position — INDEX(grid, rowMATCH, colMATCH). Swap them by accident and you read the wrong cell (or get an error if the grid is not square), so keep “row then column” in mind.
Tip: If your Excel has XLOOKUP, you may prefer it for everyday lookups. But learn INDEX + MATCH anyway: it appears everywhere in existing business spreadsheets, it does two-way grid lookups that single-direction lookups cannot, and understanding “find the position, then fetch the value” deepens how you think about every lookup.
Q. In =INDEX(B2:B4, MATCH("Cherries", A2:A4, 0)), what is MATCH’s job?
✍️ Practice
- Build the three-row product/price table and use INDEX + MATCH to fetch the price of Apples.
- Swap the columns so Price is on the left, then use INDEX + MATCH to look up a price — something VLOOKUP cannot do.
- Build the Product × Size price grid and write a two-way lookup that reads the price of a Cherries in Medium.
🏠 Homework
- Make a 6-row staff table with Name and Department, with Name in the second column and Department in the first. Use INDEX + MATCH to look up a person’s department (a left-lookup) for three different names. Then build a small product-by-month sales grid and write one two-way INDEX + MATCH that reads any product’s sales in any chosen month.