vlookup not giving correct output

[houghi]

Hello,

I have a strange problem that somehow vlookup behaves differently then
what I would ecpect. I have the following data

A B C
1 Aap Aal =VLOOKUP(A1;$A$1:$B$4;1)
2 Noot Beta =VLOOKUP(A2;$A$1:$B$4;1)
3 Gert Chello =VLOOKUP(A3;$A$1:$B$4;1)
4 Teun Gamma =VLOOKUP(A4;$A$1:$B$4;1)

However instead of getting the list Aap, Noot, Gert, Teun, I get Aap,
Noot, Aap, Teun

What am I doing wrong?

houghi

 

[Pete_UK]

You need to amend your formula like so:

=VLOOKUP(A1;$A$1:$B$4;1;0)

The zero at the end (or it could be FALSE) tells XL to look for an
exact match. If it is missing, then the table being looked up need to
be sorted, and your's isn't.

Hope this helps.

Pete

 

[Tom Hutchins]

You don't provide much information, but maybe you have omitted the final
argument for the Vlookup function. This optional argument, called
Range_lookup. determines if Vlookup looks for only an exact match or if an
approxiamte match is okay. From Excel 2003 help:

Range_lookup is a logical value that specifies whether you want VLOOKUP
to find an exact match or an approximate match. If TRUE or omitted, an
approximate match is returned. In other words, if an exact match is not
found, the next largest value that is less than lookup_value is returned. If
FALSE, VLOOKUP will find an exact match. If one is not found, the error value
#N/A is returned.

Hope this helps,

Hutch

 

[houghi]

You need to amend your formula like so:

=VLOOKUP(A1;$A$1:$B$4;1;0)

The zero at the end (or it could be FALSE) tells XL to look for an
exact match. If it is missing, then the table being looked up need to
be sorted, and your's isn't.

Hope this helps.

Sorry for the late reply. It does. thanks. I thought that leaving it out
would automaticaly make it FALSE or 0.

houghi

 

[houghi]

Hope this helps,

It does. Thanks.

Hutch

"unknown" wrote:

Mmm. I wasn't "unknown" was I?

houghi

 

[Pete_UK]

Thanks for feeding back - glad it worked for you.

If you omit the 4th parameter then it defaults to TRUE, meaning that
the source data is expected to be sorted. (Behind the scenes, XL can
carry out a faster binary search for an item if the list is sorted,
but if it isn't then it has to carry a sequential search which can be
a lot slower. I've never understood, though, why there isn't an option
to say that you want an exact search AND the data is sorted, so that a
binary search can be carried out internally)

Pete

 

[houghi]

Thanks for feeding back - glad it worked for you.

If you omit the 4th parameter then it defaults to TRUE,

Strange. I would think things to be FALSE more logical, especialy when
FALSE == 0
To me 0 is always the default. :-/

houghi

 

[Dave Peterson]

I think it goes back to Lotus 123 days.

Their =vlookup() doesn't have a 4th parameter (IIRC) and in excel, the 123
version would use True (or not specified).

So if MS wanted to be consistent with 123, then they have to make it work like
123--even if MS did enhance =vlookup() with the 4th parameter.