INDEX COLLECT formula error with criteria that starts with zero

Mike Tomei

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.

Paul Newcome

Insert a helper column on the source sheet that has this in it:

=[Model Number]@row + ""

This will convert everything to a text value so that you have the same data TYPE across all entries.

Then use the + "" bit in your MATCH function as well.

=INDEX({Cost Column}, MATCH([Model Number]@row + "", {Helper Column}, 0))

Mike Tomei

Hi Paul. Thanks for the quick response. I probably should have included this info: I'm using an INDEX COLLECT formula (rather than INDEX MATCH) because I have multiple criteria from the bill of materials that are being matched to the pricing catalog sheet. I'm matching both the manufacturer and the model number. Manufacturer is always text, but it's the model number that can be an integer starting with zero which causes my issues. I'm still having trouble using your proposed solution using the INDEX COLLECT formula. Is there another method?

Paul Newcome

You would use it very similarly.

=INDEX(COLLECT({Range To Pull}, {Manufacturer}, @cell = Manufacturer@row, {Helper Column}, @cell = [Model Number]@row + ""), 1)

Mike Tomei

Hi Paul. Unfortunately, still no luck. If the model number is strictly an integer, I get an #INVALID VALUE error message. That is now happening with integers that do and don't start with a zero. When I change the model number to be text, or even a combination of integer and text, then my INDEX COLLECT formula works well. Any idea why that's happening? I really appreciate the help with this.

Bill Sjolinder

Have you tried using the VALUE function inside the COLLECT one?

COLLECT(VALUE(cell in question))

Mike Tomei

Hi Bill. Thanks for the suggestion, but doesn't look like the VALUE function will work for me. Some of my model numbers are integers, and some are text.

harrison.saul

I am having this issue as well, has a solution been found?

Mike Tomei

Unfortunately, no. I haven't been able to find a solution that works. Please let me know if you do. Thanks.

Andrée Starå

Hi @Mike Tomei

I hope you're well and safe!

Can you share a screenshot of how it looks?

I hope that helps!

Be safe and have a fantastic week!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up, Awesome, or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Philip Robbins

I've just come across this issue in a build and have worked around it by adding helper columns to both the source and target sheets that adds a set prefix (I've used an X) to the column with the preceding zeros. I've then pointed the COLLECT function to the helper column. It's not perfect, but it got the job done.

Phil Robbins

https://www.prodactive.co.uk/

https://www.youtube.com/@Smartsheetexpert

StephenB

I'm having this issue but the source sheet isn't owned by my organization so I cannot create a helper column in it. Is there any known solution where one doesn't need to create a helper column?