closet lookup

[shabareesh]

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

 

[Claus Busch]

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

 

[joeu2004]

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.