Google Sheets – Using ArrayFormula with CountBlank or IsBlank

formulasgoogle sheets

=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.

Best Answer

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

Explained

The 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.

Example

I've added my solution to your example file.

Related Topic