Forum Discussion
Help with Index/Match/Transpose Formula
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