Excel Vlookup Tutorial Part 2 - Vlookup Syntax & Rules
In this section, we explain how to use Vlookup in Excel, by describing 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.
The following two sections of the tutorial provide practical examples of the Vlookup function to help you to
put this information to use.
The syntax for the Excel Vlookup function is:
VLOOKUP( lookup_value, table_array, col_index_num, [range_lookup] )
where the function arguments are:
The value that you want to search for
The array of data, that is to be searched for the lookup value. The Vlookup function searches in the left-most
column of the table_array
The column number of the supplied table_array, that you want to return a value from
An optional logical argument, which can be set to TRUE or FALSE, meaning:
Find the closest match below the lookup_value if the exact value is not found
(Note: with this option, the left-hand column of the table_array must be in ascending order)
Find an exact match to the lookup_value - if an exact is not found, the function returns an error
|If the [range_lookup] value is omitted, it takes 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.
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 considers all characters (including spaces) when searching for 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.
Therefore, if the [range_lookup] argument is TRUE, 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 give examples of two Vlookups, in which the [range_lookup]
argument is set to FALSE and then to TRUE.