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.

Improve this question

1 Answer 1

Reset to default
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.

Improve this answer
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, 2015 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, 2015 at 15:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

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