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

3

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.

2
  • Goodness, that is a way more elegant solution than what I was working at. Indeed named ranges will be my friend. – CamSyl Jan 14 '15 at 14:58
  • 1
    This is what I ended up using as I will be using it for a spreadsheet attached to a form. Thanks again. =ARRAYFORMULA(IF(LEN($A3:$A),IFERROR($E3:$E + VLOOKUP($G3:$G, temperature, 2, 1) + VLOOKUP($H3:$H, alkalinity, 2, 1) + VLOOKUP($I3:$I, calcium, 2, 1) - 12.1,"Missing value(s)"),)) – CamSyl Jan 14 '15 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.