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.
Generating a direct download link, as described in this article seems to work fine with the =IMPORTDATA function.
To summarize, you take the SITE_ID string from the sharing link you have:
https://drive.google.com/file/d/FILE_ID/edit?usp=sharing
Using this, you can generate the direct download link for use with the =IMPORTDATA function:
https://drive.google.com/uc?export=download&id=FILE_ID
Best Answer
It doesn't work with arrayformula - importdata, and importxml will not work it unfortunately.