INDEX COLLECT formula error with criteria that starts with zero

Options

I have a bill of materials sheet that will pull in the cost of an item based on the model number, using an INDEX COLLECT formula referencing a separate pricing catalog sheet. All works well, except for when a model number is an integer that starts with a zero, and Smartsheet automatically adds a "hidden" apostrophe to the beginning of the model number. For example, when entering a model number of 01234, Smartsheet automatically changes it to '01234 then returns an #INVALID VALUE error message in the item cost cell that contains the INDEX COLLECT formula. I've made sure that the model number columns in both the bill of materials sheet and the separate pricing catalog sheet are both Text/Number type columns, and the model numbers on both sheets start with the "hidden" apostrophe, but still no luck. How can I make this work properly? Thanks.

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!