anria
anria

Reputation: 667

Fill table from list using QUERY/FILTER

Have 2 spreadsheets:

1 spreadsheet(MATCHES) used as source

player1  player2  set1  set2  set3
ivan     jhon     6 4   2 6   6 3
mikel    ivan     6 2   6 3
mikel    jhon     6 3   7 6

2 spreadsheet(TABLE) trying to fill from source

           ivan           jhon           mikel
ivan                  6/4 2/6 6/3       2/6 3/6
jhon    4/6 6/2 3/6                     3/6 6/7
mikel     6/2 6/3       6/3 7/6

Tried to do something like(for every cell in sheet TABLE):

=IFERROR(QUERY(FILTER(MATCHES!A2:E100;MATCHES!$A$2:$A=$A2;MATCHES!$B$2:$B=B1);"SELECT C||"/"||D||" "||E||"/"||F||" "||G||"/"||H);"")

But not get any result.

Upvotes: 0

Views: 32

Answers (1)

user4039065
user4039065

Reputation:

I worked up an array formula with textjoin.

=arrayformula(textjoin(" ", true, if($A$2:$A$4=$G2, if($B$2:$B$4=H$1, substitute($C$2:$E$4, " ", "/"), ""), ""))&textjoin(" ", true, if($A$2:$A$4=H$1, if($B$2:$B$4=$G2, substitute($C$2:$E$4, " ", "/"), ""), "")))

Reversing the score depending on the relative positions of the players.

=arrayformula(textjoin(" ", true, if($A$2:$A$4=$G8, if($B$2:$B$4=H$7, substitute($C$2:$E$4, " ", "/"), ""), ""))&textjoin(" ", true, if($A$2:$A$4=H$7, if($B$2:$B$4=$G8, iferror(replace($C$2:$E$4, 1, find(" ", $C$2:$E$4), "")&"/"&replace($C$2:$E$4, find(" ", $C$2:$E$4), len($C$2:$E$4), ""), ""), ""), "")))

enter image description here

Shared spreadsheet

Upvotes: 1

Related Questions