Using a Vlookup formula within an Arrayformula

0
votes

I'm using the following formula to search a column for "Yes" and it works fine in the cell:

=VLOOKUP("Yes",INDEX(AH:AH,ROW()):INDEX(AI:AI,ROW()+30),2,FALSE)

However, my sheet is over 20000 rows and added to every day so I need to Arrayformula it. The following hasn't worked. I only want the range to search the next 30 rows OR return column two the next time it finds "Yes" in column one.

=arrayformula(IF($A4:$A<>"",VLOOKUP("Yes",INDEX(AH:AH,ROW()):INDEX(AI:AI,ROW()+30),2,FALSE),0))

Appreciate any help.

EDIT:

Below is an image of the spreadsheet. In column AK is the first formula, just a simple Vlookup. As you can see it searches column AH for the next value "Yes" and returns the value in the cell next to it.

Column AM is the same formula wrapped in an Arrayformula but as you can see it is not working.

Screenshot

1
if-statementgoogle-sheetsgoogle-sheets-formulaarray-formulasgs-vlookup
I've added an image and more text @player0 - Thomas Smith
arrayformula does not like INDEX fx thats why you getting undesired results. unfortunately just from an image is hard to tell what to change so it work for you - player0
@player0 what do you need? I think it's something to do with using row() in an array. I just tried a simple 50+row() in an array and it doesn't go higher than 52: =arrayformula(IF($A2:$A<>"",50+row(),"")) - Thomas Smith

1 Answers

0
votes
=ARRAYFORMULA(IF(LEN(A:A), IF(B1:B="yes", C1:C, ), ))

0