VLOOKUP not working consistently

Laser21

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?

Joe Goetschel

Have you tried Index Match?

https://help.smartsheet.com/function/index

Laser21

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

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.