TRANSPOSE returns an array containing the transposed values. This array contains a number of rows equal to the number of columns in the original collection and a number of columns equal to the number of rows in the original collection. The values in this array can be determined using the INDEX function.
Examples
Given the following table:
A
B
C
D
E
1
5
15
10
9
7
2
11
96
29
23
3
37
56
12
The format of the INDEX function is =INDEX(range, row-index, column-index, area-index)
=INDEX(TRANSPOSE($A$1:$E$3), 1, 1) returns 5, the value in row 1, column 1 of the transposed array (was row 1, column A, of the original collection).
=INDEX(TRANSPOSE($A$1:$E$3), 1, 2) returns 11, the value in row 1, column 2 of the transposed array (was row 2, column A, of the original collection).
=INDEX(TRANSPOSE($A$1:$E$3), 1, 3) returns 37, the value in row 1, column 3 of the transposed array (was row 3, column A, of the original collection).
=INDEX(TRANSPOSE($A$1:$E$3), 2, 1) returns 15, the value in row 2, column 1 of the transposed array (was row 1, column B, of the original collection).
=INDEX(TRANSPOSE($A$1:$E$3), 3, 2) returns 29, the value in row 3, column 2 of the transposed array (was row 2, column C, of the original collection).
=INDEX(TRANSPOSE($A$1:$E$3), 4, 3) returns 1, the value in row 4, column 3 of the transposed array (was row 3, column D, of the original collection).
For reference, the transposed table, as maintained in memory, would appear as follows.
4