Forum Discussion

Mazerati's avatar
Mazerati
Copper Contributor
Dec 21, 2023

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.

  • djclements's avatar
    djclements
    Bronze 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's avatar
      Mazerati
      Copper Contributor

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

Resources