TL;DR:
MATCH finds a position, INDEX returns the item at that position. Together they look a value up.
Break any long formula into its innermost parts and test each one in its own cell.
Ask an assistant to explain the formula on your own sheet, then confirm the result by hand.
You inherit a spreadsheet and click on a cell. The bar at the top shows =INDEX(C2:C6, MATCH("Lena", A2:A6, 0)). You can guess it looks something up, but you do not know how, and you are afraid to touch it. This is the right moment to ask for the formula to be explained, then to prove the explanation to yourself.
The sheet
Here is a small table. Column A has names, column B a class, and column C a score.
Row | A: Name | B: Class | C: Score |
|---|---|---|---|
2 | Maya | Art | 82 |
3 | Omar | Art | 91 |
4 | Lena | Bio | 77 |
5 | Ravi | Bio | 88 |
6 | Chen | Art | 95 |
Walk-through: an INDEX and MATCH formula
The formula is =INDEX(C2:C6, MATCH("Lena", A2:A6, 0)). Read it from the inside out, the way the spreadsheet evaluates it.
The inner piece: MATCH:
MATCH("Lena", A2:A6, 0)searches the rangeA2:A6for "Lena" and returns her position within that range. Maya is 1, Omar 2, Lena 3. So it returns3. The final0means exact match.The outer piece: INDEX:
INDEX(C2:C6, 3)returns the 3rd item in the rangeC2:C6. The scores are 82, 91, 77, 88, 95, so the third is77.Put together: Find Lena's position in the names, then take the score in that position. The answer is
77.Check by eye: Row 4 has Lena and a score of 77. It matches.
Notice that MATCH returns a position within the range, not a row number on the sheet. Lena is in sheet row 4, but position 3 in a range that starts at row 2. This is the most common confusion.
Test each piece in its own cell
The best way to understand any long formula is to pull the pieces apart. In an empty cell, type just =MATCH("Lena", A2:A6, 0) and see 3. In another, type =INDEX(C2:C6, 3) and see 77. Now you have evidence for each half, and you can see how the two join.
Try breaking it on purpose. Change "Lena" to "Leena". MATCH cannot find the name, so the result is #N/A, and INDEX has nothing to work with. That error tells you the lookup value does not exist in the range, which is useful to know before it happens in a real sheet.
Why people use this pair
It can look up a value to the left of the search column, which some simpler lookups cannot do.
Inserting a column does not break it, because the ranges move with the data.
Both parts are visible and testable, which makes mistakes easier to find.
Asking an assistant to explain it
A screen-aware assistant can see the formula and the data. A good request is: "Explain this formula in plain words, then walk through it with the values in my sheet." Pointing at the cell removes the need to type the formula. It should say what the lookup value is, which range it searches, and which range it returns from.
Then do your own check. Does its answer give the same number the cell shows? If it explains that the formula "returns Lena's score" and the cell shows 77, matching row 4, the explanation holds up.
Tip: Ask for the explanation using your own data, not a general example. A walk-through with real values is much easier to verify.
Reading the formula aloud
One trick that helps with any formula is to say it as a sentence. This one becomes: "from the scores, give me the item at the position where the name matches Lena." If you can say a formula in a sentence, you understand it. If the sentence feels awkward, one of the pieces is probably not what you thought, and that is the piece to test.
Write that sentence in a comment or an adjacent cell. The next person to inherit the sheet, possibly you in six months, will be grateful.
What to watch for
Before you edit someone else's formula, note what it currently returns, so you can compare afterwards. Be careful with ranges: A2:A6 and C2:C6 must be the same size, or the position will point at the wrong row. If the sheet is shared, make a copy to experiment on.
Privacy and control
Spreadsheets often hold names, grades or finances. DuckyHelper looks at your screen only while the pill is open and for that answer, and it can draw on your screen to point at the part of the formula it is talking about.
Try it yourself
Build the little table above in a blank sheet and write the formula yourself. Then change the name to Ravi and predict the answer before looking. If you said 88, you have it.
Frequently asked questions
Why the 0 at the end of MATCH?
It tells MATCH to find an exact match. Without it, MATCH may assume sorted data and give a wrong answer.
What if a name appears twice?
MATCH returns the first one it finds. If you need all of them, you need a different approach.
Is there a simpler way?
Some spreadsheets have newer lookup functions, but INDEX and MATCH works in most, and it is common in sheets you inherit.
Get started
