CODEWITHSUNDEEP
excelgoogle-sheetsexcel-formulagoogle-sheets-formula
A99
A99

Reputation: 51

How to SELECT N values ABOVE and BELOW from specific value

If I have a table:

Column A Column B Column C
1 Jane 10
2 Stewe 9
3 John 8
4 Mike 7
5 Luke 6
6 Andrew 5
7 Carl 4
8 Sasha 3
9 Ariel 2
10 Carol 1

I would like to SELECT 3 rows above and below WHERE Column B = someValue .

IF query SELECT * WHERE Column B = "Andrew" result should look like:

Column A Column B Column C
3 John 8
4 Mike 7
5 Luke 6
6 Andrew 5
7 Carl 4
8 Sasha 3
9 Ariel 2

I know how to select one row, but cant understand how to select such range. Thanks for ideas!

Upvotes: 0

Views: 2121

Answers (5)

JvdV
JvdV

Reputation: 75900

You can limit and offset inside your QUERY():

=QUERY(A1:C,"limit "&2+MIN(5,MATCH(D1,B:B,0))&" offset "&MAX(0,MATCH(D1,B:B,0)-5))

enter image description here enter image description here enter image description here enter image description here

Upvotes: 2

Erik Tyler
Erik Tyler

Reputation: 9355

This should produce what you want in all cases:

=IFERROR(FILTER(A2:C,B2:B<>"",ROW(A2:A)>=VLOOKUP("Andrew",{B2:B,ROW(B2:B)},2,FALSE)-3,ROW(A2:A)<=VLOOKUP("Andrew",{B2:B,ROW(B2:B)},2,FALSE)+3))

Of course, you can replace the two instances of "Andrew" with a cell reference (one where you type a changeable name).

This just looks up the row in a curly-bracket array formed from the names and row numbers and uses FILTER to keep results to rows between +/-3 rows of where the target name is found. If you choose the first name (or any other name), you won't get an error; because even if the target name were on Row 1 and the formula goes looking for anything "greater than or equal to 1 minus 3, all rows will be greater than a negative number. Same on the high end. You just won't get a full seven names if there aren't at least three other rows prior to or after the target row.

Upvotes: 1

Tom Sharpe
Tom Sharpe

Reputation: 34265

In Excel 365, you could try:

=INDEX(A:C,MAX(2,MATCH(D2,B:B,0)-3),0):INDEX(A:C,MIN(COUNTA(B:B),MATCH(D2,B:B,0)+3),0)

In Google sheets, on the other hand, the formula would be:

=INDEX(A:C,MAX(2,MATCH(D2,B:B,0)-3),0):INDEX(A:C,MIN(COUNTA(B:B),MATCH(D2,B:B,0)+3),0)

(spot the difference).

Excel

enter image description here

Google Sheets

enter image description here

Upvotes: 1

jweaker
jweaker

Reputation: 1

this not the best solution but it will work , you can use a helper column 'D' that contains the following formula =if(countif(INDIRECT("B"&ROW()+3&":"&"B"&ROW()-3),"Andrew")>0,TRUE,FASLE) and u can query from here like this SELECT * WHERE Column D = TRUE

Upvotes: 0

Solar Mike
Solar Mike

Reputation: 8375

Well, this was fun...

If 3 above or below are not available then blank... rolling data around is a different proposition.

Below the image is the list of formulae used.

enter image description here

So, per cell not including the data validation that is based on cells B2:B11

A14 and dragged down:

=IFERROR(INDEX($A$2:$A$11,MATCH(B14,$B$2:$B$11,0)),"")

C14 and dragged down:

=IFERROR(INDEX($C$2:$C$11,MATCH(B14,$B$2:$B$11,0)),"")

Cells B14 through B20:

=IFERROR(IF(MATCH(B$17,$B$2:$B$11,0)=3,NA(),INDEX($B$2:$B$11,MATCH(B$17,$B$2:$B$11,0)-3)),"")

=IFERROR(IF(MATCH(B$17,$B$2:$B$11,0)=2,NA(),INDEX($B$2:$B$11,MATCH(B$17,$B$2:$B$11,0)-2)),"")

=IFERROR(IF(MATCH(B$17,$B$2:$B$11,0)=1,NA(),INDEX($B$2:$B$11,MATCH(B$17,$B$2:$B$11,0)-1)),"")

=E2

=IFERROR(INDEX($B$2:$B$11,MATCH(B$17,$B$2:$B$11,0)+1),"")

=IFERROR(INDEX($B$2:$B$11,MATCH(B$17,$B$2:$B$11,0)+2),"")

=IFERROR(INDEX($B$2:$B$11,MATCH(B$17,$B$2:$B$11,0)+3),"")

Upvotes: 1

Related Questions