Index Match from off of large dataset

Casey DuBose (Jalubro Consulting)

I have a 1300 row SKU list that I am using data uploader to refresh on a recurring basis using the delete all rows and replace function.

I have a second sheet that is using an index/match to pull back certain values (like the number of items).

The reference SKU can be either an integer (123742) or a text value (12432-ac).

When I refresh the data, only the text values will return a match. The integer values will show #No Match.

However, if I go to the master SKU list, and sort by SKU, the Index/Match is able to correctly find the value.

My theory is that server is not indexing the sheet when the data is reloaded and I'm at the will of the Smartsheet gods as to when that would happen. So far, only manual intervention of sorting the sheet seems to trigger that index. However, this is just speculation.

Anyone have ideas on how to fix this issue? I've tried adding a couple of automations to the SKU list to see if that forces it to index the sheet but they do not have to seem to have worked.

Paul Newcome

I would suggest adding in a text/number column on both sheets and entering

=[SKU Column]@row + ""

This will convert everything to a text value including the integers. I have found that having all data of the same type (text vs number) provides much more consistent search results.

Paul Newcome

I would suggest adding in a text/number column on both sheets and entering

=[SKU Column]@row + ""

This will convert everything to a text value including the integers. I have found that having all data of the same type (text vs number) provides much more consistent search results.

Casey DuBose (Jalubro Consulting)

@Paul Newcome - that did it. put both the lookup and the reference to text. thanks mate!

Paul Newcome

Happy to help. 👍️