i have the data like below.

first , i pick up any val >5.0 and count SEL as 1

second, for those SEL to be 1, i want also select adjacent row (SEL=1) , then count them as 2.

Question: since there are tons of rows to check, if it is possible more fast way to find all adjacent rows around SEL=1.

thx in advance

i have the data like below.

first , i pick up any val >5.0 and count SEL as 1

second, for those SEL to be 1, i want also select adjacent row (SEL=1) , then count them as 2.

Question: since there are tons of rows to check, if it is possible more fast way to find all adjacent rows around SEL=1.

thx in advance

• google-sheets

• Are you looking for =FILTER(B2:B,A2:A=1)? – Harun24hr Commented Jan 18 at 7:46
• in ur formula, A col will select sel=1. my aim is to sel adjacent row . for example, number 5.4, his adjacent row is 1.8 and 2.45. and number 5.25, his adjacent row is 1.42 n 2.4. that is r-1 / r+1 th rows around rth row. – changjx Commented Jan 18 at 13:36
• Please edit your question and insert another that shows your manually entered desired results. Also explain why they would be the correct results. – doubleunary Commented Jan 18 at 16:43
• @changjx Would you please try my answer and feedback. – Harun24hr Commented Jan 19 at 2:12

2 Answers 2

Based on comments, you may try-

=CHOOSEROWS(B:B,LET(x,FILTER(ROW(B2:B),A2:A=1),SORT(INDEX(VSTACK(x-1,x+1)))))

Use filter() and offset(), like this:

=filter(A2:C11, B2:B11 + offset(B2:B11, -1, 0) + offset(B2:B11, 1, 0))

See filter() and offset().