Index – Match for Repeated Numbers

As you know, the combination of INDEX – MATCH gives us the content of cells two columns in the same row. If we have the repeated number in a column and we want to get all data in another column for this repeated number, is there any formula? (Please see attached excel file)
Thanks in advance for your help.

You can use this array* formula in D2:

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$21=$C2,ROW($B$2:$B$21)),ROWS($1:1))),"")

*Note that an array formula needs to be confirmed using the key combination of Ctrl-Shift-Enter (CSE), instead of the usual Enter. If you do this correctly then you will see curly brackets { } around the formula when viewed in the formula bar, but you should not try to type these yourself. If you need to edit the formula subsequently, you will need to use CSE again to confirm it.

Then copy the formula down into D3:D6 (doesn't matter if you copy it beyond this - you will just get blanks).

Hope this helps.

Pete