Reputation: 667
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
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), ""), ""), ""), "")))
Upvotes: 1