CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formula
YoYoSoHo
YoYoSoHo

Reputation: 23

How To Filter Rows Based On A List From Another Sheet? (Google Sheets)

In Excel, it is quite easy to filter rows based on a list from another sheet. But, Google Sheets does not have an advanced filter option. It therefore is not possible to filter a column by list range and criteria range in the same way that one would in Excel. So, how do you filter rows based on a list from another sheet in Google Sheets?

Worksheet to Be Filtered

Worksheet

Worksheet with Filter List

Filter List

Upvotes: 2

Views: 18681

Answers (3)

thdox
thdox

Reputation: 1644

Option 1

=FILTER(Sheet!A2:C,
        COUNTIF(Filter!A2:A, Sheet!A2:A) + COUNTIF(Filter!A2:A, Sheet!B2:B))

Option 2

=QUERY(Sheet!A2:C, 
       "select * where A matches '"&TEXTJOIN("|",TRUE,Filter!A2:A)&"' 
                    or B matches '"&TEXTJOIN("|",TRUE,Filter!A2:A)&"'")

Option 1 is using COUNTIF as explained here. The + is acting as a OR.

Option 2 is using QUERY function, with keyword matches that uses regular expression keyword1|keyword2 where | is acting as OR.

Upvotes: 0

mbomb007
mbomb007

Reputation: 4271

This is the guide I used that worked for me in Excel:

  1. Select a blank cell next to the rows you want to filter, and enter this formula =COUNTIF(Sheet2!$A$2:$A$6, A2), and press Enter, then drag the auto fill handle down to apply this formula to the cells.

  2. Select the column including the formulas, and then click Data > Filter to apply Filter function.

  3. Click the Filter icon in the formula column, only check 1 in the drop down list.

  4. Click OK. Now the rows have been filter based on the list in Sheet2.

Upvotes: 0

TheMaster
TheMaster

Reputation: 50764

  • Select A1:C11
  • Create a Filter
  • Select A1 Filter dropdown

  • Custom Formula: 

    =OR(IFERROR(MATCH(A2,customer!A$2:A$3,0),0),IFERROR(MATCH(B2,customer!A$2:A$3,0),0))

  • Both A2 and B2(HGFD and MNCD and all the respective rows below) are checked against customer!A$2:A$3. If A or B contain anything from the Customer list,It'll be shown. A2 and B2 are representative of the whole A and B column.

  • Alternatively, 

    =FILTER(A1:C11,IFERROR(MATCH(A1:A11,customer!A$2:A$3,0))+IFERROR(MATCH(B1:B11,customer!A$2:A$3,0)))

Upvotes: 4

Related Questions