# Excel combining INDEX and MATCH

I have a sheet with the following data:

``````  A         B
1 Raisa     A
2 Mellissa  A
3 Emma      A
4 Slyvia    B
5 Salina    C
6 Beula     A
7 Apolonia  F
``````

And I would like to list all people with A's, like this:

``````Raisa
Mellissa
Emma
Beula
``````

I currently use the following formula:

``````=INDEX(A1:A7;MATCH("A";B1:B7;0))
``````

Which has the output

``````Raisa
``````

How do I alter the formula so the following cells each show the 'next match'?

## 3 Answers

You can use this formula in D2

`=IFERROR(INDEX(A\$1:A\$7;SMALL(IF(B\$1:B\$7="A";ROW(B\$1:B\$7)-ROW(B\$1)+1);ROWS(D\$2:D2)));"")`

confirmed with CTRL+SHIFT+ENTER and copied down

I used ; as separators because that's the style used in the question

• +1, the route I chose not to take. – nutsch Mar 14 '13 at 22:14

If there are no duplicates in your data, keep on using your formula in the first cell (I'll assume `E1`, then use the following to copy down:

`=INDEX(offset(\$A\$1:\$A\$7;MATCH(E1;\$A\$1:\$A\$7;0);0;;);MATCH("A";offset(\$A\$1:\$A\$7;MATCH(E1;\$A\$1:\$A\$7;0);1;;);0))`

Of course, a filter could make your life easier.

• Your formula has multiple issues: 1) it should most likely be `MATCH(E1;\$B\$1:\$B\$7` not `...\$A...`. 2) replace `;` with `,` (international standard) 3) The formula will always return the second item. You probably omitted something! – Peter Albert Mar 14 '13 at 19:29
• nutsch's formula work's OK for me, Peter - once valid returns run out you get #N/A but that can be eliminated with ISERROR as per my answer.....or yours – barry houdini Mar 14 '13 at 20:08
• @PeterAlbert: 1)it should only be \$B if I didn't offset the column, 2)the question is working on French or other regional settings with semi-columns, so I stuck with that presentation. – nutsch Mar 14 '13 at 22:13
• My bad! I oversaw the parameter 1 in the OFFSET. +1 – Peter Albert Mar 15 '13 at 7:16

Similar to nutsch's answer, try this formula in `C2`:

```=IFERROR(INDEX(OFFSET(\$A\$1:\$A\$7,MATCH(C1,\$A\$1:\$A\$7,0),0),MATCH(\$E\$1,OFFSET(\$B\$1:\$B\$7,MATCH(C1,\$A\$1:\$A\$7,0),0),0)),"")
```

This assumes that you use your formula `=INDEX(\$A\$1:\$A\$7,MATCH(\$E\$1,B1:B7,0))` in `C1` - and store the value to look for (i.e. `A`) in cell `E1`.

(Note: replace the `,` with `;` according to your local settings)