5

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

Improve this question

1 Answer 1

Reset to default
8

Try formatting Water Tests as Number, Number.

Improve this answer
2
  • This worked like a charm. Any Idea why it worked without having to have similar number formats for the DiveTank
    – CamSyl
    Commented Jan 19, 2015 at 18:06
  • Pointed me in correct direction but with changing both columns to Plain Text, thanks.
    – Leo Fisher
    Commented Sep 15, 2021 at 0:22

Your Answer

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

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