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