vlookup for an approximat lookup value

[R.VENKATARAMAN]

I have a problem. I have two codes for the same stock. e.g. 1)AUROPHARMA and
2)AUROPHARM.NS.
there are subtle differences. The stock list in one case in code 1 is about
500. the other list is my stocks in code 2. now how to get both the
corresponding codes in two adjacent columns, provided it can at all be done.
.. When i tried with
vlookup(lookupvalue, database,columnno,true) to get approximate values i get
"ATUL". instead of auroph.......

I am familiar with vba and excel. Kindly advise me how to go about provided
this can be done

Mine is excel 2000

 

[arno]

Hi R.,

vlookup(lookupvalue, database,columnno,true) to get approximate
values i get "ATUL". instead of auroph.......

"approximate" values work only if the "database" is sorted. It does not
find the next best match, it simply finds the next/previous value, eg.
if you are looking for "m" you will get "b" from that table:

a
b
x
y

you will get "a" if you lookup "m" from this table:
x
a
y
b


and #NV from this one:
y
x
b
a


I use this type of lookup only with numbers, eg. to find the next best
value around 80% if I have a table like this

79,123
79,45
80,123
....

arno

 

[R.VENKATARAMAN]

thanks.

Hi R.,


"approximate" values work only if the "database" is sorted. It does not
find the next best match, it simply finds the next/previous value, eg.
if you are looking for "m" you will get "b" from that table:

a
b
x
y

you will get "a" if you lookup "m" from this table:
x
a
y
b


and #NV from this one:
y
x
b
a


I use this type of lookup only with numbers, eg. to find the next best
value around 80% if I have a table like this

79,123
79,45
80,123
...

arno