Find a number from a list of numbers

[James8761]

Hi,

I have a long list of numbers in Column E. E1:E1250. They go in number order 1-5000, but there are gaps in them. i.e. they start at 1,2,4,11,16,17,21,22,23,26,27,39,40,45,53 etc.

I type in cell B1 a number. Let’s say 4. In Cell C1 I would like to look at the figure in cell C1 (4) and then go down the list in Cells E1:E1250 and return the number that falls 12 places under 4. So in cell D1 it would return 53.

Does anyone know what the formula would be to put in D1 please? Look at C1, find C1 in the range E1:E1250, go down 12 cells and return that number in Cell D1.

Thanks for any help.

James

 

[AlanY]

try

=OFFSET($E$1,MATCH(C1,E:E,0)+11,0)

 

[James8761]

Excellent. Does exactly what I want it to. Thanks very much.

 

[AlanY]

you're welcome

 

[jtakw]

Hi,

You can also just use INDEX/MATCH without the volatile OFFSET function...

C1 uses Entire column reference, which I do Not recommend
C2 uses your Actual range:

Book1
	B	C	D	E
1	4	53		1
2		53		2
3				4
4				11
5				16
6				17
7				21
8				22
9				23
10				26
11				27
12				39
13				40
14				45
15				53
Sheet568

Cell Formulas
Range	Formula
C1	=INDEX(E:E,MATCH(B1,E:E,0)+12)
C2	=INDEX(E1:E1250,MATCH(B1,E1:E1250,0)+12)