Filling a column with a formula that references correct row without dragging down

Question

In Google Sheets, I am trying to create a formula that will automatically apply to every cell in a column. The formula refers to another column of cells in a 1:1 way, that is S3 should refer to B3, and S4 should refer to B4, so on. If I use the lower-right corner of the formula containing cell to drag down, this works correctly. The formula that fills in is unique for each cell down the column. Great!

But...I want this to be automatic. This Sheet is not user facing and should never require direct manual manipulation. If I fill in, say, 1000 cells, someday that will still run out so that isn't a real solution.

I know it is possible to use ArrayFormula to do what I want, but there is another side effect. ArrayFormula only applies the static reference in the first cell and duplicates that for every cell on the way down, instead of updating the reference on a cell by cell basis.

Here is my current, non-sufficient formula placed in S3:

=ArrayFormula(if(B3:B<>"", ArrayFormula(VLOOKUP(B3,IMPORTRANGE("[A Sheet URL]","B4:M"),{2,3,4,5,6,7,8,9,10,11,12},FALSE)),""))

The important part that isn't working is the first argument for VLOOKUP, which is always B3 for every cell all the way down the column.

Answer 1

Replace B3 from the first argument of VLOOKUP by FILTER(B3:B,LEN(B3:B)).

It's worth to say that there are several examples on this site about using VLOOKUP with an array as the first argument.

Question with a similar answer:

• Google Sheet - Summarize the best options in another table

About other kind of problem:

• Google sheets VLOOKUP issues not finding a value thats is in its range

Without answer at this time:

• VLOOKUP within VLOOKUP returning multiple results