Forum Discussion
Mazerati
Dec 21, 2023Copper Contributor
Filtering a list based on the output of another filter
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:
=FILTER(target_range!A1:B5,
target_range!A1:A5 = FILTER(source_range!C1:C3,source_range!B1:B3 = source_range!A1)
)
Where table data is:
'source_range'!A1=aaa
'source_range'!B1=aaa
'source_range'!B2=bbb
'source_range'!B3=aaa
'source_range'!C1=90
'source_range'!C2=91
'source_range'!C3=92
'target_range'!A1=90
'target_range'!A2=90
'target_range'!A3=91
'target_range'!A4=92
'target_range'!A5=90
'target_range'!B1="Cat"
'target_range'!B2="Dog"
'target_range'!B3="Bird"
'target_range'!B4="Worm"
'target_range'!B5="Duck"
Thank you for your help.
Thank you.
- djclementsBronze Contributor
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:
=LET( 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))) )
- MazeratiCopper Contributor
djclements thank you ever so much, that worked perfectly! The best secret Santa I have ever received 🙂 🎅🏻 Merry Christmas!