May 24 2021 02:52 PM
Hello Excel Forum,
Say I have an array (see below example) that is 1 x 8 matrix.
I would like to convert this to a 8 x 8 matrix (see below example) by shifting the array one column to the right.
I would like to generate this matrix by using one formula (aka dynamic array).
Thank you!
May 24 2021 03:03 PM
Let's say your array is in B2:I2, and your matrix should start in B4. Enter the following formula in B4:
=IF(ROW(B4:I11)-ROW($B$4)>COLUMN(B4:I11)-COLUMN($B$4),0,INDEX($B$2:$I$2,COLUMN(B4:I11)-COLUMN($B$4)-(ROW(B4:I11)-ROW($B$4))+1))
If you are not using Excel in Microsoft 365, select B4:I11 and enter that formula confirmed with Ctrl+Shift+Enter.