VLOOKUP stopped working when auto number hit 101.

Help can anyone help me understand why my VLOOKUP formula stopped working and all projects lower than 101 have stopped working properly?

101 working properly.


098 not working properly.

Any answers would be great!

Answers

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

    Hi @J.Barrow

    I hope you're well and safe!

    It's probably because 0** would be read as text, and 1** would be read as numbers, so you would need to have it consistent or add, for example, the VALUE function to convert it to a value regardless.

    Make sense?

    Did that work/help?

    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, Awesome, or/and as the accepted answer. 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.

  • @Andrée Starå

    I am not following the need to make them Numbers as I am using the # Auto number column format? so why would it be reading as text vs a number?

  • Leibel S
    Leibel S ✭✭✭✭✭✭

    @J.Barrow

    Check the range (make sure it encompasses the entire columns).

    also, add false to the VLOOKUP function at the end

    See if that fixes

  • J.Barrow
    J.Barrow ✭✭
    edited 05/02/23

    @Leibel S

    Thanks adding False to the end did make one cell work, but I am frustrated if that is the only solution or fix as I will need to add false to 714 formulas!!! as I have seven formulas per sheet that don't work and 102 sheets including the template. 😭

    I am still not understanding why it worked, then did not at the 101.