Getting an offset value through LOOKUP

[anmck]

Hello there,

I need to use a value in an array (which is easy to get with VLOOKUP), but in another cell, I need to use the next value in the same column...

I tried combining OFFSET with VLOOKUP, but there does not seem to be a way. I could not get the address either, which I think could be another way of attacking the problem.

For example:

ARRAY is
10.038 0.038
10.054 0.054
10.066 0.066

Please Login or Register  to view this content.

Allows me to get to the value 0.038.
However, in another cell, I need to get the next value in the same column, (0.054), (But I cannot use LOOKUP alone for this one since the increments in the reference column are not constant)

Any suggestion on how to achieve that?

Thanks!

[martindwilson]

assuming you want to match in col a then return say from column d
index(a1:d10,match(10.4,a1:a10,0)+1,4)
match(10.4,a1:a10,0) returns row so just add 1

[zbor]

Next to that cell write:

=INDEX(B2:B20,MATCH(C2,B2:B20,0)+1)

Where B is your second column of array, and C2 vlaue that VLOOKUP return

[anmck]

The trouble is, that C2 value could repeat itself in the second column. (Obviously, it is not the case in the reference column for the first VLOOKUP!)

So any other way?

[martindwilson]

i think you now need to post a workbook
the index /match given will always return the next row down as you asked from first matched value