Forum Discussion

Lucdejong's avatar
Lucdejong
Copper Contributor
Jan 11, 2021
Solved

Connecting match to a dropdown list

Hi, I have a question This is my work sheet.  What I would like is this: when Quads is selected in the dropdownlist (cel B2), i want all the excercise below the name Quads (D3) placed in the ...
excel
  • SergeiBaklan's avatar
    SergeiBaklan
    Jan 12, 2021

    Lucdejong 

    It will be better if you indicate on which version of Excel you are - are you with dynamic arrays, LET() function?

    Couple of more variants

    =INDEX($D$4:$F$36,SEQUENCE(COUNTA(INDEX($D$4:$F$36,0,MATCH($B$1,$D$3:$F$3,0)))),MATCH($B$1,$D$3:$F$3,0))
or
=INDEX($D$4:$F$36,ROW(INDIRECT("A1:A"&COUNTA(INDEX($D$4:$F$36,0,MATCH($B$1,$D$3:$F$3,0))))),MATCH($B$1,$D$3:$F$3,0))
  • SergeiBaklan's avatar
    SergeiBaklan
    Diamond Contributor
    Jan 11, 2021

    Riny_van_Eekelen , Lucdejong 

    Another DA variant

    =LET(group, $B$1, range, $D$4:$F$11, headers, $D$3:$F$3,
     col, XLOOKUP(group,headers,range), colsize, COUNTA(col),
     INDEX(col,SEQUENCE(colsize)))

     

Resources