Why must the table for Vlookup be sorted in ascending order?

  • Thread starter Thread starter Epinn
  • Start date Start date
E

Epinn

Is it true that under all circumstances I must sort the array table in
**ascending** order so that VLOOKUP will return the proper values? Is it
only necessary when I deal with ranges, say minimum score and a grade?
Appreciate explanation.



I have a column of scores and a corresponding column of grades. e.g. 90 A
80 B 70 C 60 D

I am surprised that if the table is in **descending** order and ¡§true¡¨ is
used as an argument, it doesn¡¦t work even if I key in the **exact** score
say 90, not 95, not 93. "D" is returned for 90. Wonder why.



Thank you in advance.



Epinn
 
"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"

The above is from Excel Help

Range_lookup doesn't mean *use sorted table*, but whether you want an
exact match.

You can have your table in descending order, but in that case it will have
to have *false* as the last parameter and will *only* return exact match.

The *nearest but not bigger* match (the default method) just requires them
to be in ascending order.
Thats the function they provide, use it either way.

Steve
 
Steve,

I was aware of the Excel Help that you cited before I posted previously.

You wrote: Range_lookup doesn't mean *use sorted table*, but whether you
want an
exact match.

Theoretically, that may be true. However, based on my experience with the
grade/score table, it didn't just require the table to be sorted but in the
right order i.e. ascending.

You wrote: The *nearest but not bigger* match (the default method) just
requires them
to be in ascending order.

Hmmm ...... just requires ...... I was looking for some reasoning when I
posted ......

Thanks for trying anyway.

Epinn
 
Steve,

I was aware of the Excel Help that you cited before I posted previously.

You wrote: Range_lookup doesn't mean *use sorted table*, but whether you
want an
exact match.

Theoretically, that may be true. However, based on my experience with
the
grade/score table, it didn't just require the table to be sorted but in
the
right order i.e. ascending.
Click to expand...

Isn't that *sorted*
You wrote: The *nearest but not bigger* match (the default method) just
requires them
to be in ascending order.

Hmmm ...... just requires ...... I was looking for some reasoning when I
posted ......
Click to expand...

Well the reason is that when it scans through the table, which is
ascending it stops when it it comes across the first number greater than
your *lookup value*
It then returns the last one it found, except in the the case when the
*lookup value* is less than the first element in the table.

Of course it could scan the table and handle a descending table and return
the same result - but it doesn't hence why I quoted the *help*
Whihc for once just tells it *as-it-is*

Just remember a lot of functions have been in excel / lotus for 15 years
of more.
Historicly they keep the same functionality and there is little point in
extending certain functions.
and remember those there the days when the processing power was what we
have in a mobile phone :)
 
Back
Top