VLOOKUP not working consistently
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
-
Have you tried Index Match?
Joe Goetschel | Associate Director, Smartsheet
CrossCountry Consulting - Smartsheet Partner
"The only real limitation of Smartsheet is the level of effort required to achieve your goal."
-
Tried Index Match but got the same results. Works for 800123-1 but does not work for 800123.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!