=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 dont 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 Any help would be appreciated.  Thanks is advance.
Can I use ArrayFormula with CountBlank or IsBlank if so how?
google spreadsheets;formulas
I was able to reduce the formula to the one below.Formula=ARRAYFORMULA(   IFERROR(     E3:E6 +      VLOOKUP(G3:G6, temperature, 2, 1) +      VLOOKUP(H3:H6, alkalinity, 2, 1) +      VLOOKUP(I3:I6, calcium, 2, 1) -      12.1,     Missing values   ) ) copy / paste =ARRAYFORMULA(IFERROR(E3:E6 + VLOOKUP(G3:G6, temperature, 2, 1) + VLOOKUP(H3:H6, alkalinity, 2, 1) + VLOOKUP(I3:I6, calcium, 2, 1) - 12.1,Missing values))ExplainedThe fourth parameter in the VLOOKUP formula is optional and this is what's being said about that:  Indicates whether the column to be searched (the first column of the specified range) is sorted, in which case the closest match for 'search_key' will be returned.Setting it to TRUE will result in the desired result. The IFERROR will cover those instances were a value is missing, showing the text Missing values. For very convoluted formula's it also helps to create named ranges.ExampleI've added my solution to your example file.