Vlookup returning wrong value

Vlookup returning wrong value

Vlookup returning wrong value? Computers don’t make mistakes like this so it is always good to first assume that you have made the mistake.


NEW: Go to  VLOOKUP Explanations with downloadable exercises and detailed solution


The VLOOKUP command consists of the following syntax being

=VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

The most common reason for this apparent mistake is that the user has accidentally used the incorrect value in the range_lookup argument. This can happen if you forget to include it (the default value is a TRUE) or if you type a 1 or the word TRUE.

This is because this form of a VLOOKUP searches for an approximate match and will generally find an answer but it may not be the right one! This type of VLOOKUP relies on the table array being sorted in ascending order and if this is not the case it will return the incorrect result. Be careful when sorting alphanumeric values in ascending order. The way Excel sorts and the way a human would sort is slightly different. You must use the Excel method of sorting.

If the VLOOKUP is giving an error message as if it can’t find the value, but you can clearly see that it is in the table, you may have a spaces problem. Typically one of the lookup value or table array has a space or spaces at the end of the characters. To check, click at the end of the characters and try and move your cursor left and right. The reason that this is a problem is because for Excel a space is a character and the number ‘100’ is totally different from ‘100 ‘ (note the space at the end).