Formula to get the value of a cell before and after a specific char

[bezbid]

Book1
	A	B	C	D	E
1	52.0	10		48.6	35.0
2	50.2	20
3	48.6	30
4	46.6	40	o
5	45.1	50	o
6	43.4	60	o
7	41.7	70	o
8	40.1	80	o
9	37.3	90	o
10	36.8	100	o
11	35.0	110
12	33.4	120
13	31.6	130
14	30.0	140
15	28.4	150
16	26.6	160
17	25.0	170
Sheet1

What is the formula for D1 and E1, which get the cell value of column A one row before and after, when cell in column C = "o", thanks for the help.

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E
1	52	10		48.6	35
2	50.2	20
3	48.6	30
4	46.6	40	o
5	45.07	50	o
6	43.4	60	o
7	41.73	70	o
8	40.08	80	o
9	37.25	90	o
10	36.8	100	o
11	35	110
12	33.4	120
13	31.6	130
14	30	140
15	28.4	150
16	26.6	160
17	25	170
Main

Cell Formulas
Range	Formula
D1	D1	=INDEX(A1:A100,XMATCH("o",C1:C100,0)-1)
E1	E1	=INDEX(A1:A100,XMATCH("o",C1:C100,0,-1)+1)

 

[bezbid]

Book1
	A	B	C	D	E
1	52.0	10		50.2	26.6
2	50.2	20
3	48.6	30.001
4	46.6	40.001
5	45.1	50	o
6	43.4	60	o
7	41.7	70.001
8	40.1	80
9	37.3	90	o
10	36.8	100	o
11	35.0	110	o
12	33.4	120
13	31.6	130	o
14	30.0	140
15	28.4	150.001
16	26.6	160
17	25.0	170
Sheet2

Hi Fluff,

I have a new issue with this, what is the formula for D1 and E1, to get the before and after value of Col. A, if the column B value has decimal value .001, thanks

 

[Peter_SSs]

Another option for the original question.

bezbid.xlsm
	A	B	C	D	E
1	52	10		48.6	35
2	50.2	20
3	48.6	30
4	46.6	40	o
5	45.07	50	o
6	43.4	60	o
7	41.73	70	o
8	40.08	80	o
9	37.25	90	o
10	36.8	100	o
11	35	110
12	33.4	120
13	31.6	130
14	30	140
15	28.4	150
16	26.6	160
17	25	170
Sheet1

Cell Formulas
Range	Formula
D1	D1	=XLOOKUP("o",C2:C100,A1:A99)
E1	E1	=XLOOKUP("o",C1:C99,A2:A100,,,-1)

For the new question, does this do what you want?

bezbid.xlsm
	A	B	C	D	E
1	52	10		50.2	26.6
2	50.2	20
3	48.6	30.001
4	46.6	40.001
5	45.1	50	o
6	43.4	60	o
7	41.7	70.001
8	40.1	80
9	37.3	90	o
10	36.8	100	o
11	35	110	o
12	33.4	120
13	31.6	130	o
14	30	140
15	28.4	150.001
16	26.6	160
17	25	170
Sheet2

Cell Formulas
Range	Formula
D1	D1	=XLOOKUP("*.001",B2:B100&"",A1:A99,,2)
E1	E1	=XLOOKUP("*.001",B1:B99&"",A2:A100,,2,-1)

 

[bezbid]

Book1.xlsx
	A	B	C	D	E
1	52.0	10		46.6	26.6
2	50.2	20
3	48.6	30
4	46.6	40
5	45.1	50	o
6	43.4	60	o
7	41.7	70.001
8	40.1	80.001
9	37.3	90	o
10	36.8	100	o
11	35.0	110	o
12	33.4	120.001
13	31.6	130	o
14	30.0	140
15	28.4	150.001
16	26.6	160
17	25.0	170
Sheet2

Thanks for the help. is there a formula for D1 & E1 that can apply for both conditions, that is either the .001 or o ? as the data in Col B and C changes every time

 

[Peter_SSs]

Try

bezbid.xlsm
	A	B	C	D	E
1	52	10		46.6	26.6
2	50.2	20
3	48.6	30
4	46.6	40
5	45.1	50	o
6	43.4	60	o
7	41.7	70.001
8	40.1	80.001
9	37.3	90	o
10	36.8	100	o
11	35	110	o
12	33.4	120.001
13	31.6	130	o
14	30	140
15	28.4	150.001
16	26.6	160
17	25	170
Sheet3

Cell Formulas
Range	Formula
D1	D1	=INDEX(A1:A100,AGGREGATE(15,6,SEQUENCE(ROWS(A1:A100))/((RIGHT(B1:B100,4)=".001")+(C1:C100="o")),1)-1)
E1	E1	=INDEX(A1:A100,AGGREGATE(14,6,SEQUENCE(ROWS(A1:A100))/((RIGHT(B1:B100,4)=".001")+(C1:C100="o")),1)+1)