VLOOKUP not working consistently

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



Answers

  • 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

    =IFERROR(VALUE([PartNumber]@row),[PartNumber]@row)

    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!