VLOOKUP is giving me incorrect responses

Hi,

I have created a calc sheet at work which allows colleagues to select a name from a drop down list. Once a name is selected, the cell next to the list is supposed to generate a contact id number using the formula =VLOOKUP(C2;Sheet2.$B$1:$C$243;2;0).

My problem is vlookup isnt generating the correct contact id and I cannot figure out why not! Please help!!

VLOOKUP requires that the lookup list is sorted in ascending order by the column you are using as a reference (in your case by the person’s names).

This is not entirely true.We get the result #N/A when searching for a value that is not included in the list.For example,“1”. For the value “C”, we will get the result 2 (from first row), if set the fourth parameter to the function to -1,1 or leave it blank.For the fourth parameter 0, we obtain 7

You are correct. In the small example I provided (now removed) Vlookup managed to get a value even if the list was unsorted. What I said still holds true for complex lists.

The formula seems right, so only a not correspondency between data to search and searched data. If ID are numbers be sure that all are number not numbers entered as text.

If you want the formula to work correctly, use the TRIM:

=VLOOKUP(TRIM(C2);Sheet2.$A$1:$B$243;2;0)

Or change the method of selecting values from a list