Vlookup next row

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 <[email protected]>,
"Mark" <[email protected]> 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.

I could use some similar help with this as I have tried but there are some things not working still. it might because of my A2

I have a spreadsheet made up for trips listing companies, codes, hotels, rates, etc.
What I am trying to do is select the code and then have the spreadsheet populate all hotels for that coded company.

I am using in A2 a data validation listing of all codes, so I can drop down the list and select the code. The greatest number of hotels for a company is 7.

My Example
A B C D
001 NWP EXTENDED STAY $140.00
001 NWP HOLIDAY INN $140.00
001 NWP LAQUINTA $129.00
017 AC HAMPTON INN $109.00
017 AC HOLIDAY INN $89.10
017 AC COUNTRY INN $123.49
019 AFS BW+ $120.00
019 AFS FAIRFIELD INN $104.00
030 BBB COURTYARD $100.00
047 TAZ COMFORT STES $101.00
047 TAZ QUALITY INN $84.15
047 TAZ HOLIDAY INN $80.00

What I want to return in my results is after I select from my A1 = Data Validation list of say, 017, it would look like this

A B C D
CODE CO HOTEL RATES
A2=017 AC HAMPTON INN $109.00
2 HOLIDAY INN $89.10
3 COUNTRY INN $123.49
4 #NA #NA
5 #NA #NA
6 #NA #NA
7 #NA #NA

I have used vlookup for rows 1-7, but it is always grabbing the next code/company, so there is a match problem; i have also used index and match, but the same is happening.

I think I am on the right track, but again, in the example above, rows 4-7 are still bringing in data instead of #NA.

Thoughts?

using
=VLOOKUP($A2,DATA!$A$2:$K$320,4, FALSE) = where DATA! is the next sheet with all of the er data
=INDEX(DATA!A2:$K$320,MATCH(A2,DATA!A2:A320,0)+1,4)
=INDEX(DATA!A2:$K$320,MATCH(A2,DATA!A2:A320,0)+6,4)


THANKS FOR THE LOOK SEE!