The Basics of the VLOOKUP Function

It is day #2 of VLOOKUP Week and who better to give the lay of the VLOOKUP land than Paul McFedries, author of Formulas and Functions for Excel 2010. In the following post, he breaks it down nice and neat like.

The VLOOKUP() function works by looking in the first column of a table for the value you specify. (The V in VLOOKUP() stands for vertical.) It then looks across the appropriate number of columns, which you specify, and returns whatever value it finds there.

Here’s the full syntax for VLOOKUP():

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

lookup_value: This is the value you want to find in the first column of table_array. You can enter a number, string, or reference.

table_array: This is the table to use for the lookup. You can use a range reference or a name.

col_index_num: If VLOOKUP() finds a match, col_index_num is the column number in the table that contains the data you want returned. The first column—that is, the lookup column—is 1, the second column is 2, and so on.

range_lookup: This is a Boolean value that determines how Excel searches for lookup_value in the first column:

TRUE — VLOOKUP() searches for the first exact match for lookup_value. If no exact match is found, the function looks for the largest value that is less than lookup_value, which is the default.

FALSE — VLOOKUP() searches only for the first exact match for lookup_value.

Here are some notes to keep in mind when you work with VLOOKUP():

If range_lookup is TRUE or omitted, you must sort the values in the first column in ascending order.

If the first column of the table is text, you can use the standard wildcard characters in the lookup_value argument. Use ? to substitute for individual characters; use * to substitute for multiple characters.

If lookup_value is less than any value in the lookup column, VLOOKUP() returns the #N/A error value.

If VLOOKUP() doesn’t find a match in the lookup column, it returns #N/A.

If col_index_num is less than 1, VLOOKUP() returns #VALUE! ; if col_index_num is greater than the number of columns in table_array, VLOOKUP() returns #REF!.