Index Match from off of large dataset

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.

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    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.


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!