Filter by Condition in a list using custom Formula

Question

I've googled this various different ways, but haven't found anything that has worked. I have a list that needs to be Private filtered by condition using a custom formula in google sheets.

In Sheet2 I have a list of names with shifts in Column C, in Sheet1 I have a list of shifts that correspond to a day of the week in Column B. I want the private filter to filter out by the list in Sheet1, Column B.

I have used =Regexmatch(C:C,Sheet1!B:B), I have used =Countif(C:C,Sheet1!B:B), I have used =C:C=Sheet1!$B:$B and nothing has worked please help. Thank you

Edited to add: I have included a link to a sample sheet https://docs.google.com/spreadsheets/d/1s_M7U5QwdSF1hEJ_7tPP3_PmfS_OIL27JFbceTGRUzY/edit?usp=sharing First tab has the shifts by day of the week, second tab has names with shifts. If I create a new filter view and name it Tuesday, I would like the shifts from Tuesday to be automatically be filtered based on the custom formula that pulls from the list in the "Shifts" tab.

Answer 1

You want to "filter" a list of names (with shift groups) based on shift groups.

Option#1 - Filter (menu)

=arrayformula(ifna(match(C2:C22,Shifts!$B$2:$B$5,0),0))>0

• Filter the "Shifts" column
  • select Filter by Condition
  • select Custom formula
  • enter the formula.

Using match, the formula returns the index value of a match between Shifts (Column C2:C22) and the values in the "Tuesday" column of the "Shifts" sheet (Shifts!$B$2:$B$5). If there is no match, the formula returns a value of zero. By making the condition = >0, the formula will return only those matching values.

This formula has a disadvantage - the filtered day from the Shifts sheet is hard-coded which makes it inconvenient to change.

---

Option#2 - Query: hardcoded 'day'

=query(Names!A1:C22,"select C, A, B where "&"C='"&textjoin("' OR C = '",true,Shifts!B2:B5)&"' order by C",1)

Enter this formula in any sheet, and it will return the same information as the filter option.

• Note, that filtered day from the Shifts sheet is hard-coded.

---

Option#3-Query: Select Day from Dropdown

=query(Names!A1:C22,"select C, A, B where "&"C='"&textjoin("' OR C = '",true,indirect(address(2,match($H$1,Shifts!A1:G1,0),4,true,"Shifts")&":"&address(100,match($H$1,Shifts!A1:G1,0),4)))&"' order by C",1)

• Enter the formula in any cell
• Create a Data Validation dropdown (cell H1) by referencing the range =Shifts!$A$1:$G$1.

The formula now references the "day" by matching the dropdown value. This approach makes it easy to modify data reporting.