Excel combining INDEX and MATCH

Question

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'?

Answer 1

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

Answer 2

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.

Answer 3

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)