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

1
votes

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

2
google-sheets
Screenshots added. – YoYoSoHo
After filtering, the sheet should not display any rows that contain values for farm_a OR farm_b that are not listed in the bottom sheet (via =customer!A2:A3). – YoYoSoHo
Note: The sample sheet with the list contains only two values. Obviously, that would be easy to filter manually. So, assume both sheets contain many more rows than the samples (e.g. =customer!A2:A5000) – YoYoSoHo
BTW: I tried =query(farm!A2:C7, "where A = customer!A2:A3 ",1) followed by =query(farm!A2:C7, "where B = customer!A2:A3 ",1). But, that did not work. – YoYoSoHo

2 Answers

3
votes
  • 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)))
0
votes

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.