Find number in position 'x'

[Sedge]

I need to find the following

I have a continuous group of numbers - say ...

21,5,14,61,27,85,2,14,21,5,14,61,27,85,2,14,21,5,14,61,27,85,2,14,21,5,14,61,27,85,2,14

if 21 is my last number and I want to know what number is '5 spaces' along. How do I formulate this?

The answer I want is 85

my number is 2 what number is '3 spaces' further along. How do I formulate this?

The answer I want is 5

Thanks in anticipation.

This forum is great!

Cheers Sedge.

[jindon]

UDF

=FindNumber(A1,21,5)

Please Login or Register  to view this content.

[Sedge]

Thanks Jindon.

I need further help please.

Having difficulty getting this to work. Not too familiar with macros :-)

Let me say again what I am after.
I have a continous series of numbers 21,5,14,61,27,85,2,14,21,5,14,61,27,85,2,14,21,5,14,61,27,85,2,14,21,5,14,61,27,85,2,14

I wish to input my number (which changes every time) start with 21, I input then the space for the next number, in the example I input '5'. This give me '85'.

Next I might have 61 as my number. I input the target space say 7 and am given 14 as the number. and so on.

is there a way to have the target number appear automatically after the space number is input, with out having to run the macro each time?
Can this be done with a standard formula??

Thanks again
Cheers Sedge

[jindon]

Not sure about "Automatically", though

If you place Last Number in A2, Space in B2 respectively, =FindNumber(A1,A2,B2) should give you the result.

Is this how you wanted?

[Sedge]

Jindon, Sounds like what I am after, but I'm a newbie re macros.
I have cells A1-X1 populated with

21 5 14 61 27 85 2 14 21 5 14 61 27 85 2 14 21 5 14 61 27 85 2 14

In cell A2 I have 21 and cell B2 I have 5 ....

I am having trouble loading macro. Can you talk me through it please.

Thanks again

[jindon]

It will be a different story...

If you put LastNumber in A2, Space in B2

=INDEX(A1:X1,1,MATCH(A2,A1:X1,0)+B2)

Does this work?

[Sedge]

YES YES YES!! Thankyou soo much.