The VLOOKUP() Function
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 (this 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() does not 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, VLOOKUP() returns #REF!.