VLOOKUP is giving me incorrect responses [closed]

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!!

edit retag reopen merge delete

Closed for the following reason the question is answered, right answer was accepted by Alex Kemp close date 2015-10-19 03:25:56.853608

Sort by » oldest newest most voted

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).

more

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

( 2012-12-05 11:39:19 +0200 )edit

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.

( 2012-12-05 12:59:07 +0200 )edit

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.

more

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

more