Cell linked to checkbox can do arithmetic but doesn't work in VLOOKUP

[Inclement]

Note: per m.a.riosv’s comment below here’s a sample file in Dropbox:
link. There’s a download button at the top of the right-hand sidebar. (I tried the forum file uploader but it didn’t work for me.)

---

I have checkboxes which are linked to cells named option1, option2, option3. Reference value (on) for all of them is set to 1.

If I tick the checkboxes, I can do arithmetic with the contents of the named cells, e.g. : =option1+10 gives result 11.

But if I use the linked cells in a VLOOKUP formula - =VLOOKUP(option1,lookup,2) - I get #N/A.

If I change the formula to =VLOOKUP(VALUE(option1),lookup,2), it works perfectly.

This gave me fits till I found the solution, but I still don’t understand it. If the linked cell stores values as text, why can I do arithmetic with them?

Can anyone explain? Thanks!

[mariosv]

Please edit your question to attach a sample file for test.

[Inclement]

I just tried. It went through what looked like an upload procedure, but I don’t see the file in the post.

[Inclement]

Since the uploader wouldn’t work for me, I put it on OneDrive—
— [edited] except that doesn’t work because it just wants to open it in Excel Online. Shall we try Dropbox? Click here.

[keme]

If the linked cell stores values as text, why can I do arithmetic with them?

When you explicitly use a text value in numerical context (by applying strictly arithmetic operators), Calc will attempt to interpret the value as a number. If the “connotation” is looser, text will have numerical value 0 (as will happen in the SUM() function) or be seen as larger than any number (as most likely will happen with the VLOOKUP() function).

Posting from my phone, so I can’t check whether this applies to your file, but it is the only answer I know to your question. Note, however, that you probably need an “exact match” lookup and possibly your data table is unsorted. In either case you need the optional fourth parameter to VLOOKUP() set to zero.

[Inclement]

Oh, I see. It tries to read minds, which sometimes works and sometimes doesn’t. Thank you!