CODEWITHSUNDEEP
google-sheetsformula
Lero
Lero

Reputation: 150

ArrayFormula IF ROW Not empty then iterate through formula

I'm trying to make an Array Formula to go down my Spreadsheet copying the formula but increasing the none fixed fields and removing the FALSE returns.

Here is what I currently have:

=ArrayFormula(IF(A2:A<>"",VLOOKUP(I2,Data!$A$2:$E$17,5,0)*J2))

But as it goes down it's still returning the same number meaning that I2 and J2 haven't increased and I cannot seem to remove the "FALSE" When A2:A<>"" is empty

Upvotes: 7

Views: 14388

Answers (1)

Robin Gertenbach
Robin Gertenbach

Reputation: 10776

You need to provide I2 and J2 also as a range, otherwise, as the formula is only in one cell, the reference won't change even if it is dynamic.

The false issue can be fixed by putting "" in the else clause.

=ArrayFormula(IF(A2:A <> "", VLOOKUP(I2:I, Data!$A$2:$E$17, 5, 0) * J2:J, ""))

Upvotes: 7

Related Questions