Tips for Vlookup

Vlookup Function

  1. Lookup_Value should not be more the 256 character if it be more then 256 it will return ‘#VALUE!’ error.
  2. Table_array must be vertical for Vlookup, horizontal for Hlookup.
  3. Lookup_Value should always be in first column or left most column of table array.
  4. Table_Array must always be freezed.
  5. Vlookup always return the first match, if we need to extract all records we have to apply Array_formula.
  6. Vlookup Works from left to right, if we need to apply right to left we have to inverse the range with the help of choose function.
  7. There are two type of range lookup approximate match and exact match, proper use of this function will exclude if function your workspace.
  8. The default lookup is Approximate match.
  9. For Approximate match the Table_Array first column must be sorted in ascending order.
  10. Choose function is used to concatenate the table_array in vlookup.
  11. Vlookup only search single criteria in first column of table array, if you need to search multiple values in multiple columns in table array use ‘Choose’ function in table array.
  12. Vlookup is not a case-sensitive, it will always looks for first value weather its in upper case or lower case. to find exact value use ‘EXACT’ function with vlookup.
  13. ‘col_index_num’ is a numeric value, inserting a column to Table_Array may break the formula, better to use match function in ‘col_index_number’.
  14. We can also use ‘Column’ and ‘Row’ function to make ‘col_index_num’ dynamic.
  15. Array Formula :- If we need to search multiple column in one go and column header are not matching apply array function. Like we need to search 2, 4, 5 etc column in col_index_num apply {2,4,5} and hit Control+Shift+Enter
  16. Index is much faster then Vlookup.
  17. We can lookup value from multiple table with the help of ‘IfError’ function.
  18. If we dont find lookup_Value in first column of Table_array it will through ‘#N/A’ error.
  19. If you supply value to col_index_num more then the columns in table array with will return ‘#REF!’ error.
  20. To remove unnecessary space from lookup_value use ‘Trim’ function and to remove non printable character use ‘Clean’ Function.
  21. IsError and IfError function is used to control error given by Vlookup or by other function.
  22. Vlookup with ‘Indirect Function‘ we can find record from multiple sheets with one formula.
  23. ‘Define name’ (i.e. Name Range) make it more dynamic and easy to apply.
  24. We can make table array dynamic with the help of ‘Offset Function’ or ‘Indirect’ with ‘Define Name’.

 

Note :- Index with Match can do all possible ceases that we can do with ‘Vlookup’ and ‘Hlookup’. Index with Match is advance version of Vlookup and Hlookup.

 

ExcelVBATips is a group of professionals, Managed by Chetna Sharma. Please whatApp us 8285052214 for more information or email us on ExcelVBATips@gmail.com

12 Comments to “Tips for Vlookup”

Leave a Reply