Vlookup referencing formula field

Options

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

Tags:

Answers

  • Bassam Khalil
    Bassam Khalil ✭✭✭✭✭✭
    Options

    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.

    PMP Certified

    bassam.khalil2009@gmail.com

    ☑️ Are you satisfied with my answer to your question? Please help the Community by marking it as an ( Accepted Answer), and I will be grateful for your "Vote Up" or "Insightful"

  • Andrée Starå
    Andrée Starå ✭✭✭✭✭✭
    Options

    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!

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Matt Cain
    Options

    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
    Options

    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å
    Andrée Starå ✭✭✭✭✭✭
    Options

    @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)

    SMARTSHEET EXPERT CONSULTANT & PARTNER

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

    W: www.workbold.com | E:andree@workbold.com | P: +46 (0) - 72 - 510 99 35

    Feel free to contact me for help with Smartsheet, integrations, general workflow advice, or anything else.

  • Nigel M
    Nigel M ✭✭
    Options

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

    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

    Join us at Smartsheet ENGAGE 2024 🎉
    October 8 - 10, Seattle, WA | Register now

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!