Filtering a list based on the output of another filter

Copper Contributor

I wish to select rows from a list of rows based on matching a range of values from another table.

Based on the sample data below, I wished for the source value "aaa" to return all target cells matching the reference value 90 attributed to "aaa" ("Cat", "Dog", "Duck") or, if I used "bbb", then rows matching the value 92 ("Worm"). 


My so far unsuccessful attempts have left me with this:


target_range!A1:A5 = FILTER(source_range!C1:C3,source_range!B1:B3 = source_range!A1)




Where table data is:























Thank you for your help.


Thank you.

2 Replies

@Mazerati Try using the ISNUMBER / XMATCH method as follows:


=FILTER(target_range!A1:B5, ISNUMBER(XMATCH(target_range!A1:A5, FILTER(source_range!C1:C3, source_range!B1:B3=source_range!A1))))


Or, for easier readability:


   arr, FILTER(source_range!C1:C3, source_range!B1:B3=source_range!A1),
   FILTER(target_range!A1:B5, ISNUMBER(XMATCH(target_range!A1:A5, arr)))

@djclements thank you ever so much, that worked perfectly! The best secret Santa I have ever received :) 🎅🏻 Merry Christmas!