Vlookup referencing formula field

Matt Cain · 05/19/21

I have a text/number field which is populating by using the =Left formula. I then want to reference this field using a vlookup to another sheet and pull in the corresponding information. I keep getting the #UNPARSEABLE error, and I've determined it's due to formatting, but not sure how to fix.

As an example, I have a project description field with the following information: 11922 xxxxxx-xxxxx-xxxx. I have a field that is =left(project description, 5) giving me the desired result 11922 (which is mysteriously formatted '11922, and the " ' " is giving me the issue I believe). I then do a vlookup referencing the field where 11922 sits, and trying to pull project information from a different sheet where 11922 is listed.

I know the Vlookup is good, because if I simply type 11922 into my cell I get the desired return, but if I leave it as a formula I get #UNPARSEABLE. Can Smartsheet not vlookup off of formula fields, or have I done something wrong???

Bassam Khalil · 05/20/21

Hi @Matt Cain

Hope you are fine, am sure as you said it's a formatting issue.

Please add a copy of your sheet or a screenshot (after removing or replacing any sensitive information). This will make it easier for me to provide the appropriate answer to your question.

Andrée Starå · 05/20/21

Hi @Matt Cain

I hope you're well and safe!

'11922 this means that it's interpreted as text instead of numbers.

What format are the starting values?

I hope that helps!

Be safe and have a fantastic day!

Best,

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

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

Matt Cain · 05/20/21

Hi Andree, the starting values don't seem to have the " ' " and it is a text/number column in smartsheet, so I'm not sure why the =left() formula seems to be adding it to the result? And even if I copy/paste values I can't seem to get it to drop the " ' " without manually typing over it. Perhaps the =left() formula is creating the default text string but not sure how to get what I need without it.

Matt Cain · 05/20/21

https://community.smartsheet.com/discussion/comment/292043#Comment_292043

Hi Bassam, please see attached screenshot. Today the result seems to be giving me #NOMATCH instead of the Unparseable error from yesterday, but still no luck!

Andrée Starå · 05/20/21

@Matt Cain

I'd be happy to take a quick look.

Can you 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)

Nigel M · 11/15/22

Hi,

Was this question resolved as I have the same problem:

Using VLOOKUP where the reference field is a formula: =LEFT([Work ID / Project Name]@row, 7)

If I type the same information into cell ID1 rather than a formula, then the VLOOKUP works, but I get no match when referencing the cell Work ID for Lookup in the VLOOKUP

Any ideas / suggestions

Nigel

Genevieve P. · 11/15/22

Hi @Nigel M

The LEFT formula is taking the numbers from the text string but it's keeping them as text - you can tell because it appears on the left of the cell instead of the right. In comparison, when you type the number directly into the cell, it's recognized as numerical (and appears on the right!)

You can adjust this by wrapping your initial formula in a VALUE function so it's read as numerical:

=VALUE(LEFT([Work ID / Project Name]@row, 7))

Cheers!

Genevieve

Vlookup referencing formula field

Answers

Help Article Resources

Categories