VLOOKUP not working consistently

Laser21 ✭✭
edited 03/08/24 in Formulas and Functions

I am using a VLOOKUP to another sheet. When the lookup is a number such as 800123-1 it works fine, but when the number is 800123, it does not work and I get #NO MATCH. How can I get it to work consistently for both?


  • Laser21
    Laser21 ✭✭

    Tried Index Match but got the same results. Works for 800123-1 but does not work for 800123.

  • Jason Tarpinian
    Jason Tarpinian ✭✭✭✭✭✭

    Verify that you are looking up a number against a number, or text against text (you'll see an apostrophe to begin a text value that looks like a number). If you are getting an #NOMATCH, you are most likely trying to match a number against a text.

    In the table below, my '2 is technically a text value, not a number. So it doesn't find a match in the "Match?" column. That is why all of your values with - work, because they are by default text values.

    To solves this, you can use wrap the PartNumber on each your source and target sheet with the VALUE() function


    This formula will convert any number (or any text that looks like a number) into a number, and keep text as text. Then use this helper column as your column to match against.

    I'd also echo what Joe mentioned and learn INDEX/MATCH, it is much more powerful and safe to use than VLOOKUP.

    Jason Tarpinian - Sevan Technology

    Smartsheet Aligned Partner

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!