Forum Discussion

SIRENbandit's avatar
SIRENbandit
Copper Contributor
Nov 08, 2024

Help with Index/Match/Transpose Formula

I am trying to make a table where i have Bridal party roles and the qty of that role that is in the bridal party in to a single column with each role repeated based on the qty listed int he other table. Copilot helped me get this far but it still isn't quite right.

I have a helper column in A2 =SEQUENCE(SUM(U11:U17))

and in B2 I have formula =INDEX(T$11:T$17, MATCH(ROW()-ROW($B$2)+1, MMULT(--(ROW(T$11:T$17)>=TRANSPOSE(ROW(T$11:T$17))), U$11:U$17), 1))

IT seems to be doing something close to what I want but it Best Man should only be listed once. Bridesmaid should be listed 4 times groomsmen should be listed 4 times etc.. 

Any help with my formula would be greatly appreciated.

 

Thanks!

Formulas and Functions

1 Reply

  • m_tarler's avatar
    m_tarler
    Bronze Contributor
    Nov 08, 2024

    This should fix the problem:

    =INDEX(T$11:T$17, XMATCH(ROW(A2#)-ROW($B$2)+1, MMULT(--(ROW(T$11:T$17)>=TRANSPOSE(ROW(T$11:T$17))), U$11:U$17), 1))

    I made 2 changes:

    a) changed MATCH to XMATCH (this should fix the problem)

    b) changed ROW() to ROW(A2#) to give a spill array to fill all the values with 1 formula instead of fill down

     

    that all said I CAUTION you because I see additional columns for that table that you should NOT manually enter data next to lookup values.  Basically if at some point the bride then says we have 5 bridesmaids the first 2 columns will shift but the 'corresponding' manually entered data to the right will not move.  I know in this simple example you can manually shift them down but this is how errors can happen.  Why don't you do this in reverse and manually enter column B and then generate the table in T10:U17

Resources