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.
View the Excel training video to learn how to avoid errors during implementation of Vlookup quickly and easily:
VLOOKUP: What it is, and when to use it