The Excel VLOOKUP Function

Basic Description

The Excel VLOOKUP function 'looks up' a given value in the left-hand column of a data array (or table),
and returns the corresponding value from another column of the array.

The format of the function is:

VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )

where the arguments are as follows:

lookup_value

-

The value that you want to look for, in the left-hand column of the supplied data array

table_array

-

The data array or table, that you want to search the left hand column of, for the supplied lookup_value

col_index_num

-

The column number, within the supplied array, that you want the corresponding value to be returned from

[range_lookup]

-

An optional logical argument, which can be set to TRUE or FALSE, meaning :

TRUE

-

If the function cannot find an exact match to the supplied lookup_value, it should use the
closest match below the supplied value

(Note: If range_lookup is set to TRUE, the left-hand column of the table_array must be in ascending order)

FALSE

-

if the function cannot find an exact match to the supplied lookup_value, it should return an error

Wildcards

In text-related Vlookups, the lookup_value can contain the following wildcard characters:

? - matches any single character

* - matches any sequence of characters

Vlookup Examples

Vlookup Example 1

In the spreadsheet below, columns A and B list an inventory of grocery items, and their prices,
and cell E2 of the spreadsheet shows a simple example of the Vlookup function being used to
look up the price of an item from the inventory.

A

B

C

D

E

1

Item Description

Cost ($)

Current Item:

Current Item Cost ($)

2

Tinned Tomatoes

$0.90

Cornflakes

=VLOOKUP( D2, A:B, 2, FALSE )

- returns the value $3.50

3

Tinned Tuna

$1.50

4

Cornflakes

$3.50

5

Shortcake Biscuits

$1.00

6

Toothpaste

$4.10

7

Tinned Baked Beans

$0.99

8

White Sliced Bread

$0.80

9

. . .

. . .

The above Vlookup function returns the price for "Cornflakes", which is $3.50.

In this example:

-

the lookup_value is the text string "Cornflakes", which is located in cell D2

-

the table_array is columns A-B of the spreadsheet

-

the col_index_num is set to 2, to denote that the value returned should be taken from column 2 of the table_array

-

the range_lookup argument is set to FALSE, to indicate that we only want a result to be returned if an exact
match to the lookup_value is found

Vlookup Example 2

In the spreadsheet below, columns A-C list the grades that are assigned to examination marks lying within the
ranges 0-44%, 45%-54%, etc.

Cell F2 shows the score of 52% that was achieved by the student "Anne" in an examination. The Vlookup function
in cell G2 looks up this score in column A of the spreadsheet and returns the associated grade from column C.
Note that, in this example, if the exact score of 52% is not found in column A, we want, instead, to use the
nearest value below this score.

A

B

C

D

E

F

G

1

Lower

Upper

Grade

Name

Score

Grade

2

0%

44%

F

Anne

52%

=VLOOKUP( F2, A2:C7, 3, TRUE )

- returns the value "E"

3

45%

54%

E

4

55%

64%

D

5

65%

74%

C

6

75%

84%

B

7

85%

100%

A

The above Vlookup function returns the grade for the score 52%, which is E.

In this example:

-

the lookup_value is the value 52%, which is located in cell F2

-

the table_array is the range A2-C7 of the spreadsheet

-

the col_index_num is set to 3, to denote that the value returned should be taken from column 3 of the table_array

-

the range_lookup argument is set to TRUE, to indicate that, if an exact match to the lookup_value is not found,
we want to use the closest value below the lookup_value

Further Vlookup Examples

For a practical example of the Vlookup function being used to create a variable chart, see the
Excel Variable Chart page.

The supplied col_index_num argument is < 1 or is not recognised as a numeric value

or

-

The supplied range_lookup argument is not recognised as TRUE or FALSE

#REF!

-

Occurs if either:

-

the supplied col_index_num argument is greater than the number of columns in
the supplied table_array

or

-

the formula has attempted to reference cells that do not exist.
This can occur when a user deletes a column in the spredsheet.
This can also be caused by relative referencing errors when the Vlookup is copied to other cells