I am attempting to use vlookup with values that have many decimal places.
The problem is they are close enough for an exact match so I must use an
approximate. I always get a value that is one before the value I wish to
have because it takes a value lesser than the one I am trying to match. Is
there a way I can get it to take the value in the next row? The numbers are
in numerical order.

Thanks

One way:

Instead of

=VLOOKUP(A1,J:K,2,TRUE)

use

=INDEX(K:K,MATCH(A1,J:J,TRUE)+1)


In article ,
"Mark" wrote:

I am attempting to use vlookup with values that have many decimal places.
The problem is they are close enough for an exact match so I must use an
approximate. I always get a value that is one before the value I wish to
have because it takes a value lesser than the one I am trying to match. Is
there a way I can get it to take the value in the next row? The numbers are
in numerical order.

Thanks

You should be able to use the INDEX formula combined with MATCH to be able to
do this. Assuming you're looking up the number 19.000001 in column A, and
you're returning a result from column B, the formula could look like this:
=INDEX(A1:B1000,MATCH(19.000001,A1:A1000,0)+1,2)
There are 3 parts to the index formula, first you define the range you're
looking at, second you define the row you want to return, and third you
define the column you want to return. In the formula above, A1:B1000 is
defined as the range, just like you'd do in VLOOKUP. The last number in the
formula, 2, is the column you want to return (B). The MATCH formula in the
middle section matches against your number, 19.00001, and then adds +1 to it,
in order to return the following row.

Hope this helps.
Eddie O

"Mark" wrote:

I am attempting to use vlookup with values that have many decimal places.
The problem is they are close enough for an exact match so I must use an
approximate. I always get a value that is one before the value I wish to
have because it takes a value lesser than the one I am trying to match. Is
there a way I can get it to take the value in the next row? The numbers are
in numerical order.

Thanks

This might be the easiest fix:
Move the data to the right of the first column in your lookup range up one
row. That way, even though the VLOOKUP is matching on the wrong value (by one
row) the returned value will be correct.

Does that help?

***********
Regards,
Ron


"Mark" wrote:

I am attempting to use vlookup with values that have many decimal places.
The problem is they are close enough for an exact match so I must use an
approximate. I always get a value that is one before the value I wish to
have because it takes a value lesser than the one I am trying to match. Is
there a way I can get it to take the value in the next row? The numbers are
in numerical order.

Thanks

Assuming you have a list named ListA
18.523
19.015
20.648
and you wish to look up either
19.014 or 19.016, named Near,
and get the same answer, 19.015,
then you can use this array formula
=SUMPRODUCT(--(MIN(ABS(ListA-Near))=ABS(ListA-Near))*ListA)
entered with Ctrl+Shift+Enter.