VLOOKUP
Syntax:
VLOOKUP ( lookup-value , table-array , col-index-num [ , [ range-lookup-flag ] ] )
Description: Performs a vertical search for a value in the left-most column of a table or an array, noting the row in which the matching value is found. From that row, the value from a given column is returned.
Arguments:
Name |
Type |
Description |
---|---|---|
lookup-value |
value of any type or a reference to a value of any type. |
The value to be located in the left-most column of the table. If range-lookup is |
table-array |
array, reference, name |
Designates the table of information to be searched. The values in the left-most column of table-array can be text, numbers, or logical values. The values in the left-most column of table-array shall be placed in "ascending order", as follows: ..., -2, -1, 0, 1, 2, ..., A–Z, |
col-index-num |
number |
The column number in table-array from which the matching value is to be returned. (A col-index-num of 1 returns the left-most column value in table-array, a col-index-num of 2 returns the next column in table-array, and so on.) |
range-lookup-flag |
logical |
Specifies whether |
Return Type and Value: any – The value from a given row number, where the column is determined by a search of the top row looking for a match with a given value.
However, if
An exact match is performed, but no match is found,
#N/A
is returned.col-index-num is less than 1,
#VALUE!
is returned.col-index-num is greater than the number of columns in table-array,
#REF!
is returned.lookup-value is smaller than the smallest value in the left-most column of table-array,
#N/A
is returned.
A |
B |
C |
|
---|---|---|---|
1 |
Density |
Bearings |
Bolts |
2 |
0.457 |
3.55 |
500 |
3 |
0.525 |
3.25 |
400 |
4 |
0.616 |
2.93 |
300 |
5 |
0.675 |
2.75 |
250 |
6 |
0.746 |
2.57 |
200 |
7 |
0.835 |
2.38 |
150 |
8 |
0.946 |
2.17 |
100 |
9 |
1.09 |
1.95 |
50 |
10 |
1.29 |
1.71 |
0 |
VLOOKUP(1,A2:C10,2)
results in 2.17
VLOOKUP(1,A2:C10,3,TRUE)
results in 100.00
VLOOKUP(2,A2:C10,2,TRUE)
results in 1.71
]