Using the =Left feature and then vlookup off of that result from the left

I am using the left feature to extract 6 numbers from a field. It will always be 6 character numerical figure all which are on a separate smartsheet. I am getting a no match when trying to do a vlookup off of this left.
The number that I am trying to return is a $ figure all which are on the separate smartsheet. I've asked 3 separate folks all with knowledge of smartsheet but we were unable to figure it out .
When i type in the 6 digit figure it returns a match for the $ figure. But when I used the left function the 6 digit number started on the left side of the cell instead of the right. If i type in the 6 digit number it is on the right side of the cell.
After we used the lookup formula my colleague said to use this formula but the formula only again works when we type the 6 digit number in.
Thanks in advance!
Answers
-
It sounds like the numbers in the lookup sheet are stored as numerical values but the numbers generated by the LEFT function are being stored as a text string that just looks like numbers (that's just how the LEFT function works).
wrap your LEFT function in a VALUE function to convert it to a numerical value which then in turn should be able to provide the match since they are the same data types as opposed to looking for a match between text and numbers.
=INDEX({Range To Pull}, MATCH(VALUE(LEFT(.................)), {Range To Match}, 0))
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 65.9K Get Help
- 429 Global Discussions
- 146 Industry Talk
- 486 Announcements
- 5.2K Ideas & Feature Requests
- 86 Brandfolder
- 151 Just for fun
- 74 Community Job Board
- 498 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!