Very strange behaviour of VLOOKUP in Excel 2010


S

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
 
Ad

Advertisements

C

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

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
 
C

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

Advertisements

S

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

LOOPING STRUCTURE DESIRED 4 TWO RANGES 2
Excel function to do this? 7
concatenate in combination 3
Please help! 1
VLOOKUP Problem in EXCEL 2010 3
Tricky Formulas 14
Running Average 5
1 VLOOKUP question 3

Top