[Table of Contents] [docx version]
SpreadsheetML Reference Material - Table of Contents
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 FALSE and lookup-value is a string, the wildcard characters, question mark (?) and asterisk (*), can be included in lookup-value. A question mark matches any single character; an asterisk matches any sequence of characters. To find a question mark or asterisk, type a tilde (~) before the character. |
|
table-array |
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, FALSE, TRUE. Uppercase and lowercase text is treated as equivalent. | |
|
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 HLOOKUP is to find an exact or approximate match. If TRUE or omitted, an approximate match is returned. That is, if an exact match is not found, the next largest value that is less than lookup-value is returned. If FALSE, an exact match is performed, in which case, the values in the left-most column of table-array need not be sorted. If there are two or more values in the left-most column of table-array that match lookup-value, the top-most value found is used. |
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.
[Example: Given the following data:
|
|
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
end example]