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.

1 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:

About other kind of problem:

Without answer at this time:

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.