Using a Vlookup formula within an Arrayformula

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.