Here's a link to a copy of my spreadsheet.
I am attempting to do the same formula using a VLOOKUP in four sheets to do a calculation called LSI.
In the first sheet I have the raw data from a form coming in.
In the second sheet I have used a query to just get the Dive tank data. The sheet labeled
DiveTank has the formula to calculate LSI and it works perfectly in cell
=ARRAYFORMULA(IF(LEN(A3:A),IF(row(A3:A)=3,"LSI", E3:E + VLOOKUP(F3:F, 'LSI-Index'!A4:B40,2,1) + VLOOKUP(G3:G, Alkalinity,2,1) + VLOOKUP(H3:H,Calcium,2,1) - 12.1),))
All the other sheets have the exact same set up and use a query to grab just that pool's data exactly like I did for
DiveTank. If you look at the sheet
DSM in cell
M3 it has the same formula that
DiveTank uses yet it doesn't work at all.
It seems to get stuck trying to
Named Range in the sheet
Alkalinity. I also tested the other VLOOKUPs and the only named range that seems to work is the
Temperature. I even tried just using the actual range like so:
I still get this error even if
100 is in the range:
ERROR: Did not find value "100" in VLOOKUP evaluation.