Search code examples
excelexcel-formulaexcel-365

Excel Formula to populate table based on data obtained from another table with multiple drop down lists

I'm looking for a formula to populate a table based on data obtained from another table that has multiple drop-down lists.

enter image description here

Any help will be greatly appreciated.

Solution

  • Try something along the lines of using without any recursive LAMBDA() with more efficiency.

    enter image description here

    • Formula used in cell A8

    =TRIM(TEXTSPLIT(TEXTAFTER(" "&TOCOL(TOROW(B2:C2&" "&TOCOL(B3:C3))&" "&TOCOL(B4:D4))," ",{1,2,3})," "))

    Or, bit more dynamic

    enter image description here

    =SORT(TEXTSPLIT(TEXTAFTER(" "&TOCOL(TOCOL(IFS(B2:E2<>"",B2:E2&" "&
 TOCOL(IFS(B3:E3<>"",B3:E3),3)),3)&" "&IFS(B4:E4<>"",B4:E4),3)," ",
 SEQUENCE(,MAX(TOCOL(IFS(B2:E4<>"",COLUMN(B2:E4)-1),3))))," "),3)