Very strange behaviour of VLOOKUP in Excel 2010

[s.krupinski]

Hi!

I'm working with a clean sheet in Excel 2010. Here's what I entered in my cells:

A1 = MOS B1 = 1
A2 = LOU B2 = 2
A3 = CAN B3 = 3
A4 = GIN B4 = 4
A5 = CAR B5 = 5

Then, I copy column A to column D

Finally, I type in the formula in E1:
=VLOOKUP(D1,$A$1:$B$5,2)
and copy it down in E2:E5

What one expect would be a copy of column B, right?

What I get is E1 = 5, E2 = 5, E3 = 3, E4 = 4, E5 = 3

ANY ideas..??

Greetings,

Szymon

 

[Claus Busch]

Hi,

Am Wed, 18 Sep 2013 05:51:46 -0700 (PDT) schrieb (e-mail address removed):

A1 = MOS B1 = 1
A2 = LOU B2 = 2
A3 = CAN B3 = 3
A4 = GIN B4 = 4
A5 = CAR B5 = 5

Then, I copy column A to column D

Finally, I type in the formula in E1:
=VLOOKUP(D1,$A$1:$B$5,2)
and copy it down in E2:E5

What one expect would be a copy of column B, right?

What I get is E1 = 5, E2 = 5, E3 = 3, E4 = 4, E5 = 3

you forgot the last argument for the formula:
=VLOOKUP(D1,$A$1:$B$5,2,0)


Regards
Claus B.

 

[s.krupinski]

Hi!



I'm working with a clean sheet in Excel 2010. Here's what I entered in mycells:



A1 = MOS B1 = 1

A2 = LOU B2 = 2

A3 = CAN B3 = 3

A4 = GIN B4 = 4

A5 = CAR B5 = 5



Then, I copy column A to column D



Finally, I type in the formula in E1:

=VLOOKUP(D1,$A$1:$B$5,2)

and copy it down in E2:E5



What one expect would be a copy of column B, right?



What I get is E1 = 5, E2 = 5, E3 = 3, E4 = 4, E5 = 3



ANY ideas..??



Greetings,



Szymon

Ok, it seems that your remark solved the problem. But I don't know why!

MS help says that the last argument is optional, defaulting to TRUE. If true, a similar value will be found - but I thought it will only be done in the *absence* of the actual searched value? It wasn't the case in here. Why should it find "CAR" when searching for "MOS", especially when "MOS" was in the very first position of the list??

Thanks!

Szymon

 

[Claus Busch]

Hi,

Am Wed, 18 Sep 2013 06:14:02 -0700 (PDT) schrieb (e-mail address removed):

MS help says that the last argument is optional, defaulting to TRUE. If true, a similar value will be found - but I thought it will only be done in the *absence* of the actual searched value? It wasn't the case in here. Why should it find "CAR" when searching for "MOS", especially when "MOS" was in the very first position of the list??

but MS help says also that the first column of your table has to be
sorted ascending if you don't use the optional argument
Sort the table A1:B5 by column A ascending and your formula will show
correct results.


Regards
Claus B.

 

[s.krupinski]

Hi,






but MS help says also that the first column of your table has to be

sorted ascending if you don't use the optional argument

Sort the table A1:B5 by column A ascending and your formula will show

correct results.





Regards

Claus B.

--

Win XP PRof SP2 / Vista Ultimate SP2

Office 2003 SP2 /2007 Ultimate SP2

True! When I looked into the help page again, I saw it. It's not very explicit, but it is written at some point, indeed. Thanks for pointing it out, Claus!

Best regards,

Szymon