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

Sun

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!

Paul Newcome

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))