Ask Your Question

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

asked 2020-02-29 16:15:18 +0100

Inclement gravatar image

updated 2020-03-01 03:36:28 +0100

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!

edit retag flag offensive close merge delete


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

m.a.riosv gravatar imagem.a.riosv ( 2020-02-29 20:28:08 +0100 )edit

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

Inclement gravatar imageInclement ( 2020-02-29 22:08:57 +0100 )edit

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.

Inclement gravatar imageInclement ( 2020-02-29 23:50:14 +0100 )edit

1 Answer

Sort by » oldest newest most voted

answered 2020-03-01 00:18:00 +0100

keme gravatar image

updated 2020-03-01 00:23:31 +0100

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.

edit flag offensive delete link more


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

Inclement gravatar imageInclement ( 2020-03-01 00:28:20 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower


Asked: 2020-02-29 16:15:18 +0100

Seen: 102 times

Last updated: Mar 01 '20