=IF( OR(COUNTBLANK($F3)>0,
COUNTBLANK($G3)>0,
COUNTBLANK($H3)>0,
COUNTBLANK($I3)>0),
"Missing Values",
$E3+(index(LSI_Index!$B$4:$B$40,MATCH(VLOOKUP($G3, LSI_Index!$A$4:$A$40,1),LSI_Index!$A$4:$A$40)))+(index(LSI_Index!$F$4:$F42, MATCH($H3,LSI_Index!$E$4:$E$42)))+(index(LSI_Index!$D$4:$D$38, MATCH($I3,LSI_Index!$C$4:$C$38)))-12.1)
The above works if I manually click and drag down the formula. However I would like to do something like either of these formulas below using Arrayformula()
so I don't have to 'click drag' to copy down the formula.
=ArrayFormula(IF(LEN($A3:$A),IF(OR(COUNTBLANK($F3)>0,COUNTBLANK($G3)>0,COUNTBLANK($H3)>0,COUNTBLANK($I3)>0),"Missing Values",$F3:$F+(index(LSI_Index!$B$4:$B$40,MATCH(VLOOKUP($G3:$G, LSI_Index!$A$4:$A$40,1),LSI_Index!$A$4:$A$40)))+(index(LSI_Index!$F$4:$F42, MATCH($H3:$H,LSI_Index!$E$4:$E$42)))+(index(LSI_Index!$D$4:$D$38, MATCH($I3:$I,LSI_Index!$C$4:$C$38)))-12.1),))
=ArrayFormula(IF(LEN($A3:$A),IF(OR(ISBLANK($F3:F),ISBLANK($G3:G),ISBLANK($H3:H),ISBLANK($I3:I)),"Missing Values",$F3:$F+(index(LSI_Index!$B$4:$B$40,MATCH(VLOOKUP($G3:$G, LSI_Index!$A$4:$A$40,1),LSI_Index!$A$4:$A$40)))+(index(LSI_Index!$F$4:$F42, MATCH($H3:$H,LSI_Index!$E$4:$E$42)))+(index(LSI_Index!$D$4:$D$38, MATCH($I3:$I,LSI_Index!$C$4:$C$38)))-12.1),))
I tried CountBlank()
and I've tried IsBlank()
but neither work. You can see in this spread sheet what works and what doesn't.