Doubts about the VLOOKUP function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

According to the Excel 2002 and 2003 Help documentation, the function VLOOKUP should only work if the table’s first column is in crescent order. Continuing the Help, it is informed that when type_correspondence is set FALSE, the table doesn’t have to be oriented.

I’ve made several tests on my own and all worked with non oriented tables and type_correspondece FALSE.

As the documentation is ambiguous, which problems should occur when using the VLOOKUP with a non oriented first column and type_correspondence FALSE
 
VLOOKUP will "work" on a non-sorted list if the match_type argument is
missing or TRUE, but will return the value corresponding the the largest
value that is less than or equal to the lookup value.

So if the list is

A B
1 2 6
2 5 11
3 20 5
4 30 8
5 10 9

and you enter

= VLOOKUP(10,A:B,2) or = VLOOKUP(10, A:B, 2, TRUE)

11 will be returned, since 5 is the largest value less than or equal to
10 when the list is processed in ascending order.

= VLOOKUP(10,A:B,2,FALSE)

will return 9
 
If the data on your Index column is sorted, then you can either use the optional
4th argument of TRUE or omit it altogether OR even use FALSE as well, but if the
data is not sorted, then you must use the optional 4th argument of FALSE or 0 in
order to return an accurate match of your search criteria.

--
Regards
Ken....................... Microsoft MVP - Excel
Sys Spec - Win XP Pro / XL 97/00/02/03

----------------------------------------------------------------------------
It's easier to beg forgiveness than ask permission :-)
----------------------------------------------------------------------------



Ivete said:
According to the Excel 2002 and 2003 Help documentation, the function VLOOKUP
Click to expand...
should only work if the table's first column is in crescent order. Continuing
the Help, it is informed that when type_correspondence is set FALSE, the table
doesn't have to be oriented.
I've made several tests on my own and all worked with non oriented tables and type_correspondece FALSE.

As the documentation is ambiguous, which problems should occur when using the
Click to expand...
VLOOKUP with a non oriented first column and type_correspondence FALSE?
 
I should have clarified though, that using VLOOKUP with TRUE or omitting it will
allow you to search for the nearest match to the number, whilst using FALSE or 0
will mean an exact match must be found in order to return a result.
 
Ivete,

If the fourth argument to VLOOKUP is FALSE, it doesn't matter is
the table is sorted. VLOOKUP will search for an exact match, and
if one is not found, it will return #NA. If the fourth argument
is TRUE or omitted, the first column of the lookup range must be
sorted in ascending order. Otherwise, the results are
unpredictable.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com



Ivete said:
According to the Excel 2002 and 2003 Help documentation, the
Click to expand...
function VLOOKUP should only work if the table's first column is
in crescent order. Continuing the Help, it is informed that when
type_correspondence is set FALSE, the table doesn't have to be
oriented.
I've made several tests on my own and all worked with non
Click to expand...
oriented tables and type_correspondece FALSE.
As the documentation is ambiguous, which problems should occur
Click to expand...
when using the VLOOKUP with a non oriented first column and
type_correspondence FALSE?
 
If the fourth argument
is TRUE or omitted, the first column of the lookup range must be
sorted in ascending order. Otherwise, the results are
unpredictable.
Click to expand...

An MS article invokes "unpredictable" on the same issue. Some folks read
this as if indicative of a bug in VLOOKUP and other lookup functions. In
fact, the retrieval result is predictable by the underlying algorithm, that
is, binary search, VLOOKUP invokes whenever the match-type (range_lookup in
Excel's Help File terminology) is set to TRUE. McGimpsey gives an example in
this very thread.
 
You are correct that the result returned by VLOOKUP is
predictable based on the underlying algorithm. What I meant was
that it would necessarily be what the user expected.
"Unpredictable" was an unfortunate choice of words.


--
Cordially,
Chip Pearson
Microsoft MVP - Excel
Pearson Software Consulting, LLC
www.cpearson.com
 
Back
Top