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. This is followed, in the next two parts of the tutorial by practical examples.


Vlookup Syntax

The syntax for 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 the table_array
col_index_num - The column number of the supplied table_array, that you want to return a value from
[range_lookup] - An optional logical argument, which 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: with this option, the left-hand column of the table_array must 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 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.


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. If your data is organised into rows, consider using the Hlookup function instead.
  • The col_index_num relates to the column number from the supplied table_array, not necessarily from the spreadsheet. Eg. if the table_array consists of columns D and E of the spreadsheet, and the col_index_num is equal to 2, this specifies that the result should be returned from column E
  • The function sees upper case and lower case text as being equal.
  • 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 look up will fail.
  • The Vlookup function includes 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.

Go To Excel Vlookup Tutorial Part 3 - Vlookup Example with Exact Match

Return to the Excel Vlookup Tutorial Page

Valid XHTML 1.0 Transitional Valid CSS!
Disclaimer   Privacy Policy
Copyright © 2008-2013 ExcelFunctions.net