Forum Discussion

leolapa's avatar
leolapa
Brass Contributor
Jan 24, 2023
Solved

Dynamic MATCH 2D array with one-cell spilled formula

I've got the sample table below with a list of parts/quantities and respective price quotes from 5 dummy suppliers:     What I need to accomplish here is to automatically generate a separate...
excel
Formulas and Functions
  • mtarler's avatar
    mtarler
    Jan 24, 2023

    leolapa I formatted your data as a Table and then used this:

    =LET(h,DROP(PartTable[#Headers],,2),
     s,SEQUENCE(1,COLUMNS(PartTable)-2,3),
     REDUCE(s-2,SEQUENCE(ROWS(PartTable)),
        LAMBDA(p,r, VSTACK(p,SORTBY(h,INDEX(PartTable,r,s))))))

    see attached

mtarler's avatar
mtarler
Silver Contributor
Jan 24, 2023

leolapa I formatted your data as a Table and then used this:

=LET(h,DROP(PartTable[#Headers],,2),
     s,SEQUENCE(1,COLUMNS(PartTable)-2,3),
     REDUCE(s-2,SEQUENCE(ROWS(PartTable)),
        LAMBDA(p,r, VSTACK(p,SORTBY(h,INDEX(PartTable,r,s))))))

see attached

  • leolapa's avatar
    leolapa
    Brass Contributor
    Jan 24, 2023
    Very clever solution mtarler!

    I knew a LET/LAMBDA type approach would do the trick, but I couldn't quite get there as I still need to brush up my skills on that arena.

    Thanks

Resources