VLOOKUP Doesn't Appear To Work With Auto-Number Columns/Cells

I have a main sheet containing a list of projects with the first column containing the ID of the project which is configured as an auto-number column. I am working on setting up another sheet to track projects tasks and want to be able to link a task to its associated project in the project sheet. There are select columns in the project sheet that I wish to display in the tasks sheet and am attempting to use VLOOKUP. However, when I establish the formula I consistently get "NO MATCH" returned. To confirm that I was entering the formula correctly I used a different sheet where the ID column was not auto-numbered but simply a text/number with manual entry. The formula returned the expected result in that instance, so it appears that the VLOOKUP doesn't work when the lookup column is auto-numbered. Is there another way to accomplish my goal?

Answers

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

    Hi Randy,

    I use this method all the time in my client solutions, so it should work.

    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, [email protected])

    I hope that helps!

    Be safe and have a fantastic weekend!

    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:[email protected] | P: +46 (0) - 72 - 510 99 35

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

  • I have a similar issue. The VLOOKUP is returning a value from the wrong row. To test this I changed the value to be returned from column 8 to column 1 and sure enough it doesn't return the proper value. In this exampe the AutoNum column has a value of I327, and when the VLOOKUP returns column 1 it returns I3 rather than I327. For most of the values in the AutoNum column I get #NO MATCH even though there are matching values.

    =VLOOKUP([Column_ID]@row, {SheetName Range 2}, 1

    [Column_ID] = I327 which has a match in SheetName

    Range 2 includes 8 columns, I want the value from column 8, I just changed it to 1 to see what it would return as it was providing the wrong value.

    The screen shot is of the AutoNum column configuration in SheetName.


  • I did more testing, I created two simple sheets to test and it works properly. More testing on the original sheet shows that it is only reading the first two characters of the SheetName, AutoNum column. Any ideas what could cause that?

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

    Hi @Martin van Gogh

    I'd be happy to take a quick look.

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

    I hope that helps!

    Be safe and have a fantastic weekend!

    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:[email protected] | P: +46 (0) - 72 - 510 99 35

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

  • Frenk
    Frenk ✭✭

    Hi

    I have the same problem as described in initial description.

    Any idea

    Frank

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!