We have learnt how to
use the Vlookup function, Vlookup with named ranges, Vlookup with Lists
and Vlookup using absolute references. Today let's have a look at how
to avoid errors during the implementation of Vlookup:
- Take care that your is placed in columns and there
are no blank columns in-between
- Data in the Excel worksheet cells should not containg
trailing or leading invisible spaces because "Tom" is not the same a "
Tom" or "Tom ". You can use the CLEAN or TRIM function to correct such
problems in your data
- If your lookup value and table array data are in the
same data then defining the table array using standard methods
is not a problem but if the table array is outside your worksheet data
or in another worksheet then you will need to access the table array
using an absolute reference
- The 'return' value must be in a column that exixts.
For example, instead of 2 you may write -2, 3 or 12 in a hurry and that
column may not exist, then you'll get a '#VALUE!' error
- If you want to use the value TRUE in the range_lookup
parameter then your data in the first lookup column must be arranged in
asending order or Vlookup may return erratic values. If Vlookup doesn't
find an exact match, then the next largest value which is less than the
lookup_value is displayed.
- Your data values in column one or the lookup_value
column need not be sorted if you the range_lookup boolean parameter is
FALSE.
- In most real world problems you'll use the
range_lookup parameter as FALSE and Vlookup will always find an exact
match.
- If you have more than one value in the first column
of the table_array that matches the lookup_value in the table array
then Vlookup will display the first found value. If the function
doesn't find an exact match it will return '#N/A'
- If you are searching for numbers or dates
then the data stored in the first column of the table should
not be text. In such cases Vlookup will return an incorrect value or
totally unexpected value.
- In case of text values lookup you can use the wild
card characters like '?' for one character and '*' for multiple
characters. For example if you wish to lookup "Tom" or "Tony" then you
can use 'T*' ot 'T??'. In the latter case, of course, you'll only find
'Tom'.
Vlookup is really not as difficult as it is made out to be if you take
care of the parameters and avoid such simple mistakes.