How to look up a value in a list and return multiple corresponding values

Hi All,

I wonder if there is a way to look up a particular value (in the attached ) and to return multiple corresponding values?

On the attached are three tabs (the original is three different workbooks), what I'm trying to do is something like below, but this only returns the first dataset and I need all sets for each client.

=IFERROR(INDEX('Their Data'!$A$2:$A$8,MATCH(A2,'Their Data'!$B$2:$B$8,0)),"")

for instance, if Bob client 1 has more than one order it'll show as per attached.

Thanks for you help.

CLASH

multiple_corresponding_values(1).xlsx
Please see above.

Hi,

Enter this array formula in B2 of the Collation sheet and copy across and down:

=IFERROR(INDEX(Orders!$B$2:$C$14,SMALL(IF(Orders!$A$2:$A$14=Collation!$A2,ROW(Orders!$A$2:$A$14)-MIN(ROW(Orders!$A$2:$A$14))+1),ROUNDUP(COLUMNS($A:A)/2,0)),1+(MOD(COLUMNS($A:A),2)=0)),"-")

Regards