Filter matched list in Google Sheets QUERY

Question

I have a team leaderboard that I am improving. Here are the steps I am trying to streamline:
1. Import table using the ImportHTML function
2. Select needed columns using the Query function
3. Filter the information in the columns to only include the names that match the names on a specified pre-existing list using the Filter function.
4. Sort the data by score.

Here is a link to my example workbook: https://docs.google.com/spreadsheets/d/1F0w-7bW8Wbh-eJubyBubeMM_yMzzcSZw28R0OJ-l8q8/edit#gid=1646904068

You can see from the "IS Warm-Up" sheet the former way that this was being done. I am experimenting with streamlining it in "IS-Individual" before streamlining the other sheets as well. The "Names" sheet contains the team members that I want to pull the results for.

   =Sort(
  Filter(
    QUERY(
      IMPORTHTML("https://ct.thecmp.org/app/v1/index.php?do=match&task=getMatchResultsDetail&MatchId=18095&EventId=7&AwardId=1","TABLE",1),
     "SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13" & match(Names!A:A,COL1),
Match(Names!A:A,0)),2,True))

I've also tried:

=Sort(Filter(QUERY(IMPORTHTML("https://ct.thecmp.org/app/v1/index.php?do=match&task=getMatchResultsDetail&MatchId=18095&EventId=7&AwardId=1","TABLE",1),"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13"),Match(QUERY(IMPORTHTML("https://ct.thecmp.org/app/v1/index.php?do=match&task=getMatchResultsDetail&MatchId=18095&EventId=7&AwardId=1","TABLE",1),"SELECT Col1, Col2, Col3, Col4, Col5, Col6, Col7, Col8, Col9, Col10, Col11, Col12, Col13"),Names!A:A,0)),2,True)

player0 player0 · Accepted Answer · 2019-07-01T00:02:49

=SORT(FILTER(QUERY(IMPORTHTML(
 "https://ct.thecmp.org/app/v1/index.php?do=match&task=getMatchResultsDetail&MatchId=18095&EventId=7&AwardId=1",
 "TABLE", 1),
 "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13"),
 MATCH(INDIRECT("Names!A1:A"&COUNTA(QUERY(IMPORTHTML(
 "https://ct.thecmp.org/app/v1/index.php?do=match&task=getMatchResultsDetail&MatchId=18095&EventId=7&AwardId=1",
 "TABLE", 1),
 "SELECT Col1"))+1), 0)), 2, 1)

this would work too if the imported table won't change in size:

=SORT(FILTER(QUERY(IMPORTHTML(
 "https://ct.thecmp.org/app/v1/index.php?do=match&task=getMatchResultsDetail&MatchId=18095&EventId=7&AwardId=1",
 "TABLE", 1),
 "SELECT Col1,Col2,Col3,Col4,Col5,Col6,Col7,Col8,Col9,Col10,Col11,Col12,Col13"),
 MATCH(Names!A1:A89, 0)), 2, 1)

Filter matched list in Google Sheets QUERY

1 Answers