Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.newusers
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Why must the table for Vlookup be sorted in ascending order?
"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 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) |
#3
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Why must the table for Vlookup be sorted in ascending order?
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" 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 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) |
#4
Posted to microsoft.public.excel.newusers
|
|||
|
|||
Why must the table for Vlookup be sorted in ascending order?
On Sat, 12 Aug 2006 02:11:10 +0100, Epinn
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" 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 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Why is the order of my data table opposite from graph? | Charts and Charting in Excel | |||
Digits in ascending order ? | Excel Worksheet Functions | |||
graph displayed in ascending order of value? | Charts and Charting in Excel | |||
Graph values in ascending order | Excel Discussion (Misc queries) | |||
pivot table in data source order | Excel Discussion (Misc queries) |