CODEWITHSUNDEEP
google-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-querygs-vlookup
user1721451
user1721451

Reputation: 247

Vlookup or Index and return multiple adjacent rows in next column in Google Sheets

I'm not sure this is possible. I have column A which I'm searching for text, then I want to return column B + the 5 adjacent rows below and as separate cells, not a sum. Some of those may be blank. Here's an example of what I'm working with. The position of the number in column B can change, however, I will always want a fixed amount of rows. I have tried using the index but I was hoping for 1 formula to return multiple rows. Here's what I have =INDEX(B21:B190,MATCH($L$1,A21:A190,0)+1) (then +2, +3, +4, +5 etc...:

enter image description here

Upvotes: 0

Views: 1154

Answers (1)

player0
player0

Reputation: 1

try:

=ARRAYFORMULA(IFNA(QUERY({VLOOKUP(ROW(A3:A), 
 IF(A3:A<>"", {ROW(A3:A), A3:A}), 2, 1), B3:B}, 
 "select Col2 where Col1 = '"&D3&"'", 0)))

0

Upvotes: 1

Related Questions