Array Index / Offset Query

GUIDO22

Registered User.
Local time
Today, 21:33
Joined
Nov 2, 2003
Messages
515
Hello All!
I have an array of numbers represented in adjacent cells in a spreadsheet as so...

1,2,4,6,7,9,15,23,28,32 for example.

I also have a pair of numbers. One number the array reference, the other the offset.

I wish to be able to return the value in the array at (reference + offset).

So, if I had the following :
Reference : 6 & Offset : 4 the (returned) cell value would be 23 from the example above...
ie. find value 6 in the array, then move '4' places to the right ..

Any ideas how I can represent this functionality as a formula in a spreadsheet please...?

Thanks
 
not a real expert bu try using the index and match functions

something like =index(A1:a10,1,match(6,A1:A10,0)+4)
 
Thanks CJ, this put me on the right track. For the record, herewith the formula

=INDEX($A$1:$A$151,H4+MATCH(F4,$A$1:$A$151 ))

A1:A151 is my array, F4 is the value in the array that I am searching for and H4 is the offset I wish to apply.....
 

Users who are viewing this thread

Back
Top Bottom