Excel formula that assists in looking for sequence

[brian8989]

https://imgur.com/a/5HUdECj


Dear Experts,

With reference to the attached table, is there any excel formula that could assist to find the 10th number after Number 1 in Column A (which is 14).

I would greatly appreciate if you could assist on this. Thank you!


Regards,
Brian

 

[tyija1995]

=OFFSET(A8, 10, 0) would return the 10th row after the 1 on column A

 

[Fluff]

How about

Excel 2013/2016
	A	B	C	D	E	F	G	H	I	J	K	L
1			1	8	10	5	3	7	4	6	2	9
2	3	1	15	18	14	13	4	8	19	6	2	16
3	10	2	4	14	20	8	13	16	6	18	19	12
4	7	3
5	5	4
6	11	5
7	17	6
8	1	7
9	15	8
10	2	9
11	4	10
12	19	11
13	13	12
14	6	13
15	8	14
16	18	15
17	16	16
18	14	17
19	12	18
20	20	19
21	9	20
Template

Cell Formulas
Range	Formula
C2	=INDEX($A$2:$A$21,MATCH($B2,$A$2:$A$21,0)+C$1)

 

[brian8989]

Thank you so so so so much for helping! @tyija1995 @Fluff

@Fluff Is there any formula to make it restarts from Number 3 in Column A in order to avoid reference error problem?

https://imgur.com/a/VMckQ4H

Thank you!

 

[Fluff]

How about
=INDEX($A$2:$A$21,IF(MATCH($B2,$A$2:$A$21,0)+C$1>ROW($A$21)-1,MATCH($B2,$A$2:$A$21,0)+C$1-ROW($A$21)+1,MATCH($B2,$A$2:$A$21,0)+C$1))

 

[brian8989]

@Fluff It works!!! Thank you so so so so much!!!!

 

[Fluff]

You're welcome & thanks for the feedback