Leading the way in Microsoft Office Development

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

 -

VLOOKUP worksheet function

Returns the value in a column that matches a value in the first column of a table.

This function looks up a value in the first column and returns a value in the same row but in a different column.

The "range_lookup" argument is very important as this determines if an exact match or an approximate match is found.

If "range_lookup" = False then only exact matches are returned.

If "range_lookup" = False and there is no match then, #N/A! is returned.

If "range_lookup" = True then approximate values will be returned if no match is found.

If "range_lookup" = True then the first column of your table should be sorted in ascending order.

Looking up a value that exists (e.g. "Mark")

This example looks up the value "Mark" (in the first column) and returns the corresponding value from column "C".

Mark exists in the first column so the "range_lookup" argument is irrelevant.

When the "range_lookup" argument is False, the function returns the correct value.

In this case "Fidler" is returned.

When the "range_lookup" argument is True, the function returns the correct value.

In this case "Fidler" is returned.

Looking up a value that does not exist (e.g. "Richard")

This example tries to look up the value "Richard" (in the first column) and returns the corresponding value from column "C".

Richard does not exist in the first column so the value that is returned depends on whether "range_lookup" is True or False.

When the "range_lookup" argument is False, the function will only return exact matches.

In this case #N/A! is returned.

When the "range_lookup" argument is True, the function will find return the nearest matching value.

In this case "Ellis" is returned because Paul is the nearest value that is less than Richard.

Looking up a value that exists more than once (e.g. "James")

This example looks up the value "James" (in the first column) and returns the corresponding value from column "C".

James exists more than once in the first column so the value that is returned depends on whether "range_lookup" is True or False.

When the "range_lookup" argument is False, the function returns the first matching value.

In this case "Bell" is returned.

When the "range_lookup" argument is True, the function returns the last matching value.

In this case "Jones" is returned.

The last matching value is returned because the table is sorted into ascending order in the column we are searching in.

If the table was not sorted into ascending order by column 2, a different value will be returned.

When the table is not sorted, the second row that contains "James" is found.

Things to Remember

Your data table does not have to contain column labels.

When no match is found and you want to return approximate values, the first column of your table must be sorted in ascending order.

This function is not case sensitive when searching for text strings.

This function can be used in place of several IF() functions.

You cannot use this function to lookup a value based on both the row and the column headers. This can be done using a combination of the OFFSET() and MATCH() functions which is described in the Cross Reference page of this section.