Formulas and Functions

Chapter 9 Reference Functions 223
=INDEX(TRANSPOSE($A$1:$E$3),1,1) returns 5, the value in row 1. column 1 of the transposed range
(was row 1, column A, of the original array).
=INDEX(TRANSPOSE($A$1:$E$3),1,2) returns 11, the value in row 1, column 2 of the transposed range
(was row 2, column A, of the original range).
=INDEX(TRANSPOSE($A$1:$E$3),1,3) returns 37, the value in row 1, column 3 of the transposed range
(was row 3, column A, of the original range).
=INDEX(TRANSPOSE($A$1:$E$3),2,1 returns 15, the value in row 2, column 1 of the transposed range
(was row 1, column 2, of the original range).
=INDEX(TRANSPOSE($A$1:$E$3),3,2) returns 29, the value in row 3, column 2 of the transposed range
(was row 2, column C, of the original range).
=INDEX(TRANSPOSE($A$1:$E$3),4,3) returns 1, the value in row 4, column 3 of the transposed range
(was row 3, column D, of the original range).
Related Topics
For related functions and additional information, see:
“Listing of Reference Functions” on page 206
“Value Types” on page 36
“The Elements of Formulas” on page 15
“Using the Keyboard and Mouse to Create and Edit Formulas” on page 26
“Pasting from Examples in Help” on page 41
VLOOKUP
The VLOOKUP function returns a value from a range of columns by using the left
column of values to pick a row and a column number to pick a column in that row.
VLOOKUP(search-for, columns-range, return-column, close-match)
 search-for: The value to nd. search-value can contain any value type.
 columns-range: A range of cells. range is a reference to a single range of cells,
which may contain values of any type.
 return-column: A number that species the relative column number of the cell
from which to return the value. return-column is a number value. The leftmost
column in the range is column 1.
 close-match: An optional value that determines whether an exact match is required.
close match (TRUE, 1, or omitted): If there’s no exact match, select the column with
the largest top-row value that is less than the search value. Wildcards can’t be used
in search-for.
exact match (FALSE or 0): If there’s no exact match, return an error. Wildcards can
be used in search-for.