Home |
Search |
Today's Posts |
#1
|
|||
|
|||
closet lookup
Hi
I have a problem with looking up the data sample set B1B291A4 4 B1B291H4 4 B1B291A5 5 B1B291H5 5 B1B291A7 7 B1B291H7 7 B1B291A9 9 B1B291H9 9 B1B291A12 12 B1B291H12 12 B1B291A14 14 B1B291H14 14 B1B291A16 16 B1B291H16 16 .. lookup value - B1B291A6 I need to get the next available value if the lookup is not matched. if i use the function = vlookup(A1,A:B,2,1) i am getting 16 rather i need to get 5 as output... this is creating problem if we have alpha numeric lookup... Any help would be appreciated in solving this.. Thanks Shabs |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
closet lookup
Hi shabs,
Am Tue, 12 Jun 2012 15:23:06 +0000 schrieb shabareesh: if i use the function = vlookup(A1,A:B,2,1) i am getting 16 rather i need to get 5 as output... you have to sort your table ascending Regards Claus Busch -- Win XP PRof SP2 / Vista Ultimate SP2 Office 2003 SP2 /2007 Ultimate SP2 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
closet lookup
"shabareesh" wrote:
sample set B1B291A4 4 B1B291H4 4 B1B291A5 5 B1B291H5 5 B1B291A7 7 B1B291H7 7 B1B291A9 9 B1B291H9 9 B1B291A12 12 B1B291H12 12 B1B291A14 14 B1B291H14 14 B1B291A16 16 B1B291H16 16 lookup value - B1B291A6 I need to get the next available value if the lookup is not matched. if i use the function = vlookup(A1,A:B,2,1) i am getting 16 rather i need to get 5 as output... In order to use any of the lookup functions (VLOOKUP, MATCH, LOOKUP) to find the "closest" match, the lookup data (first column) must in ascending order. (MATCH also has an option that requires descending order.) Note that Excel sort order is not the same as sorting by the ASCII code. If the lookup data are not in ascending order, the lookup might return bogus results, or it might return a #N/A error (no match). In rare instance, it might return the correct results, but only by coincidence. If you know how a binary search algorithm works, you'll know why. However, the lookup functions really return results based on an exact match or the next __lower__ match. That might not be your definition of "closest". Moreover, when you have alphanumeric lookup data, the numeric part is treated like characters, not numbers. For example, A0123 is less than A122 because the character zero is less than the character one. That might not meet your expectations of an "alphanumeric" lookup. |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Vertical lookup with a lookup value that returns multiple matches | Excel Programming | |||
lookup help. lookup result based on data in 2 columns | Excel Worksheet Functions | |||
LOOKUP FUNCTION? (LOOKUP VALUE BEING A TIME RENERATED FROM A FORMU | Excel Discussion (Misc queries) | |||
closet to 0 | Excel Discussion (Misc queries) | |||
Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions |