Forum Discussion

TerrorTot38's avatar
TerrorTot38
Copper Contributor
Feb 02, 2022
Solved

Filtering Data Using not Exact Match

Spoiler
I am trying to extract / filter a list of names based on if a player is attending. For example if Player A is FALSE(not playing) only show the last 3 pairs highlighted in the example below.

I have a list of names of players. Column A (Players) Column B (Attending T / F)

Player A - FALSE
- Player B - TRUE
- Player C - TRUE
- Player D - TRUE

I then have a list of pairs for example. Column D (Pairs not played)

- Player A & Player B
- Player A & Player C
- Player A & Player D
- Player B & Player C
- Player B & Player D
- Player C & Player D

Thus far I have 
Excel Formula:
=VLOOKUP("*"&J2&"*",$M$2:$M$104,1,FALSE)

For example Kim and Kimberley are 2 different values. 

 

  • Hi 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)

     

     

     

3 Replies

  • Martin_Weiss's avatar
    Martin_Weiss
    Bronze Contributor

    Hi 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)

     

     

     

    • TerrorTot38's avatar
      TerrorTot38
      Copper Contributor
      Thank you very much for the help with this solution as both look great. My only problem is with the number of players we have there is up to 191 unique pairings.

      I'm happy to share a one drive file with a bigger data set as this works fine for 4 players.
      • Martin_Weiss's avatar
        Martin_Weiss
        Bronze Contributor

        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.

Resources