INDEX and MATCH: learn the halves before the formula
MATCH finds the position, INDEX returns what is there. Each half on its own first, then nested, then the two-way lookup XLOOKUP does not do as neatly.
INDEX and MATCH is two functions doing one job. Most people learn it as a single shape they copy, and then cannot fix it when it breaks.
So learn the halves first. Each one works on its own, in its own cell.
Put this table in A1 to D4.
| Row | A | B | C | D |
|---|---|---|---|---|
| 1 | Region | Jan | Feb | Mar |
| 2 | North | 1200 | 1400 | 1100 |
| 3 | South | 900 | 1100 | 1350 |
| 4 | East | 1500 | 1250 | 1600 |
MATCH answers "where is it?"
MATCH takes a value and a list. It returns the position in that list. A number, nothing else.
=MATCH("South",A1:A4,0) → 3
South is the 3rd item in A1:A4. Not row 3 of the sheet — the 3rd item of the range you gave it. Those are the same here and will not always be.
The 0 means exact match. Never leave it out. Without it MATCH assumes 1. That means "the largest value at or below this, on a sorted list". On unsorted data it is a wrong answer with no error.
INDEX answers "what is in position 3?"
INDEX takes a range and a number. It returns the item at that position.
=INDEX(C1:C4,3) → 1100
The 3rd cell of C1:C4 is C3, and C3 holds 1100. That is the whole function.
Now put one inside the other
You want February for South. INDEX can give you the answer if you tell it the position. MATCH works out the position. So MATCH goes where the number goes.
=INDEX(C1:C4,MATCH("South",A1:A4,0)) → 1100
Read it from the inside out. MATCH turns into 3. The formula becomes INDEX(C1:C4,3). That returns 1100.
That is the trick for debugging it too. Select just the MATCH part in the formula bar and press F9. Excel replaces it with the number it produces. Press Escape, never Enter.
Why bother, when VLOOKUP is one function?
It looks left
VLOOKUP returns something to the right of the column it searched. INDEX and MATCH do not know what right means. The two ranges are independent.
=INDEX(A1:A4,MATCH(1600,D1:D4,0)) finds which region had 1600 in
March. VLOOKUP cannot do this without moving columns.
It survives an inserted column
=VLOOKUP("South",A1:D4,3,FALSE) returns February. Insert a column
between A and B. The formula still says 3, and the third column is now January. The answer is
wrong and nothing warns you.
INDEX and MATCH hold addresses, not counts. Insert a column and Excel moves C1:C4 to D1:D4 for you. The answer stays right.
It reads two columns instead of a whole block
VLOOKUP is given the whole table. INDEX and MATCH are given two columns. On a sheet with hundreds of columns and thousands of rows, that is a real difference in speed.
On a normal sheet it is not. Do not choose between them for this reason.
The two-way lookup: the reason to still learn it
XLOOKUP has taken over most of the jobs above. This is the one it does not do as neatly.
You want the number where a region meets a month, with both chosen by name.
=INDEX(A1:D4,MATCH("East",A1:A4,0),MATCH("Feb",A1:D1,0)) →
1250
Give INDEX a block instead of one column, and it takes a row number and a column number. The first MATCH finds the row. The second finds the column. Neither is typed by hand, so the formula keeps working when the table grows.
Put the two names in cells and point at them instead of quoting them. Then the whole thing is a small report you drive from a dropdown.
The four mistakes
- Leaving the 0 out of MATCH. The commonest one. On unsorted data it returns a wrong number and no error.
- Ranges of different heights. MATCH on A1:A4 and INDEX on C2:C4 are off by one. Start both at the same row, every time.
-
Mixing a whole column with a partial one.
=INDEX(C:C,MATCH("South",A1:A4,0))works by accident, because both start at row 1. Change one to A2:A4 and it breaks. Keep them the same shape. - Forgetting that MATCH returns #N/A when it finds nothing. That is correct behaviour. Wrap the outside in IFNA when a miss is expected.
Questions people ask
Which goes on the outside, INDEX or MATCH?
INDEX. It is the one that returns the answer you want. MATCH only ever produces a number, and that number is an argument to INDEX.
Can MATCH search a row instead of a column?
Yes. A range is a range. =MATCH("Feb",A1:D1,0) searches across the
heading row and returns 3.
What do the 1 and -1 in MATCH do?
1 finds the largest value at or below yours, on a list sorted small to large. Use -1 to find the smallest value at or above yours, on a list sorted large to small. Both are for bands and tiers. For everything else, 0.
Is INDEX and MATCH worth learning if I have XLOOKUP?
Yes, for two reasons. It works in every version of Excel, so a file you share cannot break. And the two-way lookup above is still cleaner than the XLOOKUP version. Learn XLOOKUP for your own files and this for everybody else's.