Offset Match Vlookup query

[aquelious]

Hi.

Quite some time ago I used Offset Match (and possibly Index) in a formula, that helped me collate some data in a spreadsheet.

In the example attached, I have a list of players and the teams they have scored against (Column A-B).

What I would like to do is use Offset Match Index to give me what I have in columns G-J, where the formula searches for the player name, then gives the first team they scored against (column H), then the formula looks for another occurrence of their name and populates the team next to that occurrence in column I etc etc.

I know it can be done, but I just cant remember how to do it

Any help would be very much appreciated.

 

[jasonb75]

In H2, then drag right and down as needed

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$18)/($A$2:$A$18=$G2),COLUMNS($H2:H2))),"")

 

[aquelious]

In H2, then drag right and down as needed

=IFERROR(INDEX($B:$B,AGGREGATE(15,6,ROW($B$2:$B$18)/($A$2:$A$18=$G2),COLUMNS($H2:H2))),"")

@jasonb75 brilliant. That absolutely works a treat. Thank you.