RESIZEARRAY resizes an array to a given number of rows and columns, allowing for a default value where columns and rows are out-with the original array
Excel Formula:
=LAMBDA(array,r,c,defaultValue,
LET(
rA,ROWS(array),
cA,COLUMNS(array),
seqR,SEQUENCE(IF(r=0,rA,r)),
seqC,SEQUENCE(,IF(c=0,cA,c)),
return,IF(seqR>rA,defaultValue,IF(seqC>cA,defaultValue,INDEX(array,seqR,seqC))),
return)
)| Lambda - Last Cell and Split.xlsx | ||||||||||
|---|---|---|---|---|---|---|---|---|---|---|
| B | C | D | E | F | G | H | I | |||
| 1 | Original Array | Resized array | ||||||||
| 2 | 1 | 2 | 3 | 1 | 2 | 3 | 0 | |||
| 3 | 4 | 5 | 6 | 4 | 5 | 6 | 0 | |||
| 4 | 7 | 8 | 9 | 7 | 8 | 9 | 0 | |||
| 5 | 10 | 11 | 12 | 10 | 11 | 12 | 0 | |||
| 6 | 0 | 0 | 0 | 0 | ||||||
| 7 | 0 | 0 | 0 | 0 | ||||||
Sheet1 | ||||||||||
| Cell Formulas | ||
|---|---|---|
| Range | Formula | |
| B2:D5 | B2 | =SEQUENCE(4,3) |
| F2:I7 | F2 | =RESIZEARRAY(B2:D5,6,4,0) |
| Dynamic array formulas. | ||
Upvote
0
