Index Match, Inconsistent 0 before number 3 digit number.

Meagan80 · 01/06/22

I have two different 3rd party associates that use Smartsheet's. We have a number to represent stores. One party uses a 4 digit number so when the number is 3 digits, they add a 0 before it. The other does not and only uses the 3 digits. I have formulas such as Index match to pull data from their sheets but have now run into the problem of how pulling the data by the store number from both.

Is there an addition to my formula I can use that will ignore the 0 and still pull the data from a 0123 and a 123 the same?

Thank you for any help!

Jeff Reisman · 01/06/22

There are probably a few ways to do this. Without seeing your formulas/sheets it's hard to tell which way would be best.

I'm thinking you need a nested IF statement in there somewhere. The logic would be: If the left most digit in the store number is a zero ( LEFT({Store Number}, 1) = 0 ), then only read the three righthand numbers in the store number ( RIGHT({Store Number}, 3) ), otherwise, use the whole store number.

Jeff Reisman · 01/10/22

https://community.smartsheet.com/discussion/comment/315320#Comment_315320

Happy I could help!

I thought of one other thing that would work, and it's a more simple solution: The VALUE function.

Wrap the VALUE function around your INDEX/MATCH, and if a result comes back that starts with a leading 0 such as 0123, the VALUE function changes it to its true numeric value of 123. Alternatively, reference the cell containing your INDEX/MATCH results, and find its value:

Using the VALUE function also eliminates issues with some cells possibly being text while others are numeric.

Jeff Reisman · 01/06/22

There are probably a few ways to do this. Without seeing your formulas/sheets it's hard to tell which way would be best.

I'm thinking you need a nested IF statement in there somewhere. The logic would be: If the left most digit in the store number is a zero ( LEFT({Store Number}, 1) = 0 ), then only read the three righthand numbers in the store number ( RIGHT({Store Number}, 3) ), otherwise, use the whole store number.

Meagan80 · 01/07/22

Thank you! I will try that.

Jeff Reisman · 01/10/22

https://community.smartsheet.com/discussion/comment/315320#Comment_315320

Happy I could help!

I thought of one other thing that would work, and it's a more simple solution: The VALUE function.

Wrap the VALUE function around your INDEX/MATCH, and if a result comes back that starts with a leading 0 such as 0123, the VALUE function changes it to its true numeric value of 123. Alternatively, reference the cell containing your INDEX/MATCH results, and find its value:

Using the VALUE function also eliminates issues with some cells possibly being text while others are numeric.

Meagan80 · 01/10/22

I love this! Thanks so much! I can use this in some other issues I was having as well.

Index Match, Inconsistent 0 before number 3 digit number.

Best Answers

Answers

Help Article Resources

Categories