Home »
Excel-Vlookup-Tutorial »
Vlookup-Syntax-And-Rules
Excel Vlookup Tutorial Part 2 - Vlookup Syntax & Rules
Vlookup Syntax & Rules
This page provides details of the Vlookup function syntax and rules of use.
You might find it useful to bookmark this page to refer back to, during later sections of the tutorial.
This will be followed by practical examples of the Vlookup function to help you to put this information to use.
Vlookup Syntax
The syntax of the Excel Vlookup function is:
VLOOKUP( lookup_value, table_array,
col_index_num, [range_lookup] )
where the function arguments are:
| lookup_value |
- |
The value that you want to search for.
|
| table_array |
- |
The array of data that is to be searched for the lookup_value.
Note: The Vlookup function searches in the left-most column of this array.
|
| col_index_num |
- |
An integer, specifying the column number of the supplied table_array,
that you want to return a value from.
|
| [range_lookup] |
- |
An optional logical argument that describes what the function should return in the event
that it does not find an exact match to the lookup_value.
The [range_lookup] can be set to TRUE or FALSE, meaning:
| TRUE |
- |
If an exact match is not found, use the closest match below the
lookup_value.
Note: if this option is used, the left-hand column
of the table_array must be in ascending order.
|
| FALSE |
- |
If an exact match to the lookup_value is not found,
return an error.
|
If the [range_lookup] value is omitted, it uses the default
value of TRUE.
|
A reminder of the Vlookup syntax, in the form of a handy Vlookup Quick Reference Card, can be printed from the
Microsoft Office website.
Vlookup Rules
It is important that you understand the following rules when using the Vlookup function in Excel:
- The data in the supplied table_array must be organised in columns.
- The function sees upper case and lower case text as being equal (i.e. a lookup of the string "TEXT"
will match the string "text").
- The Vlookup function does not recognise numbers and text representations of numbers as being equal.
Therefore, if your lookup_value is equal to the text string "10", but
the values in the table_array are numeric, the function will fail to
find a match.
- The Vlookup function compares all characters (including leading/trailing spaces) when
checking if two values are a match. Therefore, the two text strings, "text" (no spaces) and "text "
(with trailing spaces) are not an exact match.
Rules for the Vlookup Using an Exact Match
If the [range_lookup] argument is set to FALSE, the Vlookup searches for an
exact match to the lookup_value. If an exact match is not found, the function
returns the #N/A error.
In this case, the following rules apply:
- If the lookup_value is duplicated in the left-hand column of the
table_array, the function uses the first match that it encounters.
- If the lookup_value is a text string, you can use wildcard characters,
where the character ? matches any single character and the asterix, * matches any set of characters.
For example, a lookup_value "t*" would match any text string beginning
with the character "t".
|
Rules for the Vlookup Using the Closest Match
If the [range_lookup] argument is set to TRUE (or omitted), the
lookup_value is matched to the closest value below or equal to this value.
In this case, the following rules apply:
- The left hand column of the supplied table_array
must be in ascending order. If it isn't, the function may return unpredictable results.
- If the lookup_value is smaller than all of the values in the left-hand
column of the table_array, the Vlookup function returns the #N/A error.
|
The next two sections of the Vlookup tutorial describe two Vlookup examples, in which an exact
match is required and a closest match is required.
Continue to Part 3 of the Excel Vlookup Tutorial >>