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 match...
djclements
Dec 21, 2023Bronze 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)))
)
Mazerati
Dec 22, 2023Copper Contributor
djclements thank you ever so much, that worked perfectly! The best secret Santa I have ever received 🙂 🎅🏻 Merry Christmas!