I've tried to find a formula that would return the diagonal of an array A1:D4 as follows:
A1 B2 C3 D4
I found this one, which returns a column, but I can't find out how to order the values in a row instead.
=INDEX(A1:D1,,ROWS($1:1))
(paste in the columns below)
A TRANSPOSE on the result would of course do the trick, but I'd appreciate something slightly more elegant. Thanks!
If you use the exact range you mentioned (A1:D4) then this should work
=ARRAYFORMULA(TRIM(QUERY(IF(ROW(A1:D4)=COLUMN(A1:D4), A1:D4,),,ROWS(A1:A4))))
(No need to fill down.)
If you want to get the diagonal values of any range (here: B11:E14), try
=ARRAYFORMULA(TRIM(QUERY(IF(ROW(B11:E14)-ROW(B11)+1=COLUMN(B11:E11)-COLUMN(B11)+1, B11:E14,),,ROWS(B11:B14))))
How about this, then as you pull it across it will go down the diagonal
=index($A$1:$D$4,columns($A1:A1),columns($A1:A1))
or the equivalent of your original formula would be
=index(A1:A4,COLUMNS($A1:A1))
Using undocumented FLATTEN life saving function:
=TRANSPOSE(
QUERY(
FLATTEN(
ARRAYFORMULA(
IF(COLUMN(A1:D4) = ROW(A1:D4), A1:D4, "")
)
),
"WHERE Col1 IS NOT NULL",
0
)
)
And if the range is not at the top left corner:
=TRANSPOSE(
QUERY(
FLATTEN(
ARRAYFORMULA(
IF(
COLUMN(F15:I18) - COLUMN(INDEX(F15:I18, 1, 1)) + 1 = ROW(F15:I18) - ROW(INDEX(F15:I18, 1, 1)) + 1,
F15:I18,
""
)
)
),
"WHERE Col1 IS NOT NULL",
0
)
)
Intentionally used INDEX(F15:I18, 1, 1) instead of just F15 to have only the range F15:I18 (e.g. it could be a named range) as a parameter.
get all diagonals:
∕
=SORT({ FLATTEN(ARRAYFORMULA(ROW(A5:E9) + COLUMN(A5:E9))), FLATTEN(A5:E9) })
∖
=SORT({ FLATTEN(ARRAYFORMULA(ROW(A5:E9) - COLUMN(A5:E9))), FLATTEN(A5:E9) })
