Ask Your Question

VLOOKUP is giving me incorrect responses [closed]

asked 2012-12-04 16:07:01 +0200

irksy gravatar image

updated 2013-06-20 16:35:40 +0200

manj_k gravatar image


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 flag offensive 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

3 Answers

Sort by » oldest newest most voted

answered 2012-12-05 10:50:18 +0200

Pedro gravatar image

updated 2012-12-05 12:57:29 +0200

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

edit flag offensive delete link 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

JohnSUN gravatar imageJohnSUN ( 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.

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

answered 2012-12-04 19:08:58 +0200

m.a.riosv gravatar image

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.

edit flag offensive delete link more

answered 2012-12-05 09:00:41 +0200

JohnSUN gravatar image

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


Or change the method of selecting values from a list

Error Validate

edit flag offensive delete link more

Question Tools


Asked: 2012-12-04 16:07:01 +0200

Seen: 7,638 times

Last updated: Dec 05 '12