Excel Filter function with multiple searches in the same column

Question

I am trying to use the Filter function to copy multiple groups of data from a master 2D array. I have a column with 5 categories. Each category has a random number of rows. I want to search through the master array based on a list of search categories and copy those rows to a new 2D array. For example here is my master 2 D array:

                 A           B           C

Row 1 Apple 4 Empire

Row 2 Pear 10 Bosch

Row 3 Apple 18 Golden

Row 4 Orange 3 Navel

Row 5 Grape 68 Concord

Row 6 Plum 6 Red

Row 7 Pear 92 Anjou

Row 8 Orange 1 Clementine

My search list (1D array) is

Row 10 Pear

Row 11 Plum

Row 12 Grape

Row 13 Orange

I want to search for matches in column A and then copy the entire row to a new @D array starting in Row 20. The result should look like:

                  A           B           C

Row 20 Pear 10 Bosch

Row 21 Pear 92 Anjou

Row 22 Plum 6 Red

Row 23 Grape 68 Concord

Row 24 Orange 3 Navel

Row 25 Orange 1 Clementine

FILTER(A1:C8, A1:A8=A10:A12,""). I need to pass the search array to the FILTER function or put the FILTER function inside another function to effectively get the filter to loop through my master array multiple times. Any suggestions would be appreciated.

Answer 1

Hi,

In cell G2, enter this formula

=SORT(FILTER(A2:C9,ISNUMBER(MATCH(A2:A9,E2:E5,0))),1)

Hope this helps.

Answer 2

If you don't mind a different sort order:

=FILTER(A1:C8,ISNUMBER(MATCH(A1:A8,A10:A13,0)))

Answer 3

Excel 365 Pro Plus with Power Pivot and Power Query.

Limit processing of data to the short list.

Sort results by its order.

Added more stuff to justify using PP/PQ.

No formulas, no Let().

Amenable to easy, automatic expansion.

https://www.mediafire.com/file/mrlg2zld3fzq9hg/02_08_22.xlsx/file

https://www.mediafire.com/file/3lai3f6t5rpx2lf/02_08_22.pdf/file

Answer 4

Hi

in L3:

=LET( 

    data,     B3:D10, 

    criteria, F3:F6, 

    arr,      FILTER( 

                data, 

                ISNUMBER( 

                    XMATCH( INDEX(data,,1), criteria ) 

                ) 

            ), 

    SORTBY( 

        arr, 

        XMATCH( INDEX(arr,,1), criteria ) 

    ) 

)

Answer 5

Hi Hans

Thanks for the quick feedback. Unfortunately, I need the results grouped instead of interspersed. I tried what you said and here is what I got:

Pear	10	Bosch
Orange	3	Navel
Grape	68	Concord
Plum	3	Red
Pear	92	Anjou
Orange	1	Clementine

Is there another function that will effectively loop through the matrix 4 times? (4 times for my search list of 4 items)