Heres a link to a copy of my spreadsheet I am attempting to do the same formula using a VLOOKUP in 4 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 M3. =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 pools 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 VLOOKUP the Named Range in the sheet LSI-Indexcalled 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 VLOOKUP(H3:H, 'LSI-Index'!C4:D38,2,1) I still get this error even if 100 is in the range.ERROR: Did not find value 100 in VLOOKUP evaluation.Any insight would be appreciated.
Google sheets VLOOKUP issues not finding a value thats is in its range
google spreadsheets
Try formatting Water Tests as Number, Number. 