ArrayFormula IF ROW Not empty then iterate through formula

Question

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

Answer 1

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, ""))