Feb 02 2022 02:11 PM - edited Feb 02 2022 02:12 PM
=VLOOKUP("*"&J2&"*",$M$2:$M$104,1,FALSE)
Feb 03 2022 12:01 AM
SolutionHi @TerrorTot38
under the assumption, that the names in a pair are in one singel cell and always separated by a &-sign, this could be one solution:
=AND(VLOOKUP(LEFT(A7;FIND("&";A7)-2);$A$1:$B$4;2;FALSE)=TRUE;VLOOKUP(RIGHT(A7;LEN(A7)-FIND("&";A7)-1);$A$1:$B$4;2;FALSE)=TRUE)
The formula is tricky because it needs to split the pair first and then do the comparison agains the list of players.
It would be much easier, if you have the names of the pairs in two separate columns:
=AND(VLOOKUP(A7;$A$1:$B$4;2;FALSE)=TRUE;VLOOKUP(B7;$A$1:$B$4;2;FALSE)=TRUE)
Feb 03 2022 05:09 AM
Feb 03 2022 11:35 PM
Hi @TerrorTot38
in the end, the only thing that you probably need to do is to adjust the cell range in the example to your needs.
Feb 03 2022 12:01 AM
SolutionHi @TerrorTot38
under the assumption, that the names in a pair are in one singel cell and always separated by a &-sign, this could be one solution:
=AND(VLOOKUP(LEFT(A7;FIND("&";A7)-2);$A$1:$B$4;2;FALSE)=TRUE;VLOOKUP(RIGHT(A7;LEN(A7)-FIND("&";A7)-1);$A$1:$B$4;2;FALSE)=TRUE)
The formula is tricky because it needs to split the pair first and then do the comparison agains the list of players.
It would be much easier, if you have the names of the pairs in two separate columns:
=AND(VLOOKUP(A7;$A$1:$B$4;2;FALSE)=TRUE;VLOOKUP(B7;$A$1:$B$4;2;FALSE)=TRUE)