exceldynamic-arrays

Excel MS365: 2-D dynamic array creation problem


I have an Excel problem that boils down to the following:

Given the input range A1:B3 (a 2-D array), is it possible to use only formulas to create the output D1:K3? I would like to use the input values row-wise to create a range and then fill the output accordingly (e.g. {1,4} as an input means that positions 1 through 4 on that output's row should be filled). You can assume that the highest value in the input array determines the width of the output.

input array example (A1:B3) and desired output example (D1:K3)

input array example (A1:B3) and desired output example (D1:K3)

I was able to create an extremely convoluted solution with a SEQUENCE/SWITCH combination, but it only works for each row separately. I don't think it will help, but I can reproduce it here if needed.


Solution

  • One way:

    =LET(d,A1:B3,MAKEARRAY(ROWS(d),MAX(d),LAMBDA(r,c,AND(c>=INDEX(d,r,1),c<=INDEX(d,r,2))+0)))