Dec 21 2023 08:14 AM
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.
Dec 21 2023 08:37 AM
@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)))
)
Dec 22 2023 12:50 AM
@djclements thank you ever so much, that worked perfectly! The best secret Santa I have ever received :) 🎅🏻 Merry Christmas!