VLOOKUP in a separate sheet not working

GrahamW

Hi,

I have created a sheet where I have a series of checkboxes on each row where each checkbox represents a particular service that we provide. I then have a column function that is meant to add up all the services selected by checking the checkboxes. The function works in general, but I am experiencing a really weird "bug". If I use certain words in the lookup function as the lookup value the function returns "#NO MATCH". I have cross checked the words I'm using to look up the values with the "key" in the reference sheet and they appear to be correct.

I initially had the "code" as the primary column which I thought might be the problem, so I rebuilt the sheet with the "code" as a single select dropdown and the service name as the primary column. This didn't help either.

The only way that I can get the VLOOKUP to work is to change the lookup values entirely. When I changed the lookup values to C1,C2,C3,C4, and C5 and updated the reference sheet to those codes in the key column it all works fine. If I use the lookup values I wanted to use (Full, Essential, Basic, AppMaint and AppSupport) it fails and I get the "#NO MATCH" error. All I'm doing is changing the lookup value in the double quotes for each lookup and the matching value in the reference sheet so I can only assume that there is something weird going on with the lookup.

Here is the formula that works (with the lookup code in column 1, the name in column 2 and the price that I am trying to extract in column 3)

=IF(Full@row, VALUE(VLOOKUP("C1", {Managed Services Price Book CodeNamePrice}, 3)), 0)+IF(Essential@row, VALUE(VLOOKUP("C2", {Managed Services Price Book CodeNamePrice}, 3)), 0)+IF(Basic@row, VALUE(VLOOKUP("C3", {Managed Services Price Book CodeNamePrice}, 3)), 0)+IF([App Maint]@row, VALUE(VLOOKUP("C4", {Managed Services Price Book CodeNamePrice}, 3)), 0)+IF([App Support]@row, VALUE(VLOOKUP("C5", {Managed Services Price Book CodeNamePrice}, 3)), 0)

Here is the formula that fails:

=IF(Full@row, VALUE(VLOOKUP("Full", {Managed Services Price Book CodeNamePrice}, 3)), 0)+IF(Essential@row, VALUE(VLOOKUP("Essential", {Managed Services Price Book CodeNamePrice}, 3)), 0)+IF(Basic@row, VALUE(VLOOKUP("Basic", {Managed Services Price Book CodeNamePrice}, 3)), 0)+IF([App Maint]@row, VALUE(VLOOKUP("AppMaint", {Managed Services Price Book CodeNamePrice}, 3)), 0)+IF([App Support]@row, VALUE(VLOOKUP("AppSupport", {Managed Services Price Book CodeNamePrice}, 3)), 0)

Any ideas as to what is going on?

Andrée Starå

Hi @GrahamW

I hope you're well and safe!

Can you describe your process in more detail and maybe share the sheet(s)/copies of the sheet(s) or some screenshots? (Delete/replace any confidential/sensitive information before sharing) That would make it easier to help. (share too, andree@workbold.com)

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!