Excel Vlookup Tutorial Part 2 - Vlookup Syntax & Rules

This page describes 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.

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. 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, which 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

-

Find the closest match below the lookup_value if the exact value is not found.

Note: if this option is used, the left-hand column of the table_arraymust be in ascending order.

FALSE

-

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 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.