About Microsoft Excel

Avoiding Mistakes During Vlookup

Home Back Next



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.

Home Back Next