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

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

On Fri, 11 Aug 2006 16:20:34 +0100, Epinn <[email protected]_SPAM>
wrote:

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



--
Steve (3)

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


"SteveW" <[email protected]> wrote in message
news:op.td4l0ibievjsnp@enigma03...
> "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
>
> On Fri, 11 Aug 2006 16:20:34 +0100, Epinn <[email protected]_SPAM>
> wrote:
>
> > 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
> >
> >
> >
>
>
>
> --
> Steve (3)

On Sat, 12 Aug 2006 02:11:10 +0100, Epinn <[email protected]_SPAM>
wrote:

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

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

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

>
> Thanks for trying anyway.
>
> Epinn
>
>
> "SteveW" <[email protected]> wrote in message
> news:op.td4l0ibievjsnp@enigma03...
>> "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
>>
>> On Fri, 11 Aug 2006 16:20:34 +0100, Epinn <[email protected]_SPAM>
>> wrote:
>>
>> > 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
>> >
>> >
>> >
>>
>>
>>
>> --
>> Steve (3)
>
>



--
Steve (3)