In this post I have listed issues that people often face while using vlookup
To begin with, here is the syntax for the vlookup function
VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])
This image shows how the function is used
[range_lookup] is an optional parameter and takes the values True or False
When the parameter is entered as True (or omitted)
- The array needs to be sorted in ascending order on the first column
- If exact match is not found, the next largest value that is less than look_up value is returned
When the parameter is entered as False, vlookup finds an exact match
1. On carrying out vlookup with range_lookup as false, the value returned is #N/A, though I am sure that the lookup_value is contained in column1 of table_array, what could be the problem?
One of the likely cause of this error is that lookup_value contained in table_array is preceded or succeeded by empty spaces or unseen characters. One way to find this out is through the function exact. You could key in =exact(lookup_value, corresponding value from table_array), if it returns a false, you know that they are not the same values. This often happens when you import a table from an external system. One of the ways to remove spaces is through Trim function. Refer an earlier post on how to trim
2. Vlookup returns a wrong corresponding value from the table_array.
The most likely cause is that you have either entered range_lookup as true or omitted it and that table_array is not sort in ascending order on column1. Either enter range_lookup as false, or sort table_array in ascending order on column1.
3. I am looking for an approximate value through vlookup with range_lookup as true. Have sorted the table_array in ascending order, yet vlookup returns #N/A. Can you figure out what could be wrong?
vlookup with range_lookup true uses the largest value in column 1 of 
able_array that is less than or equal to look_up value. So if look_up value is say 50 and the table array starts with 100, then vlookup would return an error #N/A
For instance if lookup_value is 155,
vlookup(155,A2:B5,2,true) would return 2.04
4. I am carrying out a vlookup with a 10 column table_array, vlookup returns a #REF! error.
Make sure that col_index_num does not exceed 10 and you should be fine.
General observation: When the table_array is stored in a separate workbook, make sure that workbook remains in the same location as when the vlookup was created
Let me know if you have faced any other kind of problem while using vlookup.
Useful Excel Tips 