[Table of Contents] [docx version]

SpreadsheetML Reference Material - Table of Contents

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 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

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, 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]