New FILTER function - multiple criteria

Copper Contributor

I'm playing with the new FILTER function:
https://support.office.com/en-us/article/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759?NS=EXC...
 
I'm filtering a table with the following formula: =FILTER( A5:E1002 , D5:D1002 = H5)
 
It returns any rows where the entry column D is equal to H5.
 
If I want to include H6 as well, I can write =FILTER( A5:E1002, (D5:D1002=H5)+(D5:D1002=H6)).
 
How could I pick up any row where column D  appears in column H?
 
I'd like an expression like "D5:D1002 is in H5:H20". Any simple way of doing this?

1 Reply

Never mind: got it!

 

Settled on =FILTER( A5:E1002, COUNTIF(H5:H20,D5:D1002) )

 

It will filter the table by rows where COUNTIF(H5:H20,D) is true.