|
The
syntax you use to write the
Vlookup
formula is: '=VLOOKUP(lookup_value,table_array,col_index_num,range_lookup).
The lookup_value
is the
value
in the first column of the Excel data.
The table_array
is two
or
more columns of data in the worksheet. It can be referenced by a range
like 'A1:D7' or you can give it a name like 'myinventory'. The values
in the first column of table_array are the values searched by
lookup_value. These values can be text, numbers, or logical values. Uppercase and lowercase text are
equivalent.
The col_index_num
is
the
column number in the table_array from which the matching value must be
returned. The left-most column has the col_index_num 1, next column has
2, and so on.
The range_lookup
is a
logical value that specifies whether you want VLOOKUP
to find an exact match or an approximate match. This value is the
source of most confusion in vlookup. If you set this value to 'TRUE'
or omit it an exact or approximate match is returned. If an exact match
is not found, the next largest value that is less than lookup_value is
returned. Also under these conditions the values in the first column of
table_array must be placed in ascending sort order
or
VLOOKUP may not give the correct value. If you set the range_lookup
value to 'FALSE', vlookup will find an
exact match and
you need not sort the data in the first column of the table_array. In
case there are two or more values in the first column of table_array
that match the lookup_value, the first value found will be used. If you
get an error like '#NA' or '#VALUE', vlookup has either not found an
exact match or you have looked for a value in col_index_num less than
1. If the col_index_num is greater than the number of columns in
table_array, VLOOKUP returns the #REF! error value.
When searching text values
in the first
column
of table_array, ensure that the data in the first column of table_array
does not have leading spaces, trailing spaces, inconsistent use of
straight ( ' or " ) and curly ( ‘ or “) quotation marks, or nonprinting
characters. In these cases, VLOOKUP may give an incorrect or unexpected
value. You can correct such errors by using the TRIM or CLEAN functions.
If range_lookup is FALSE and
lookup_value
is
text, then you can use the wildcard characters, question mark (?) and
asterisk (*), in lookup_value. A question mark matches any single
character; an asterisk matches any sequence of characters. If you want
to find an actual question mark or asterisk, type a tilde (~) preceding
the character.
|