#NO MATCH result with INDEX/MATCH formula referencing Auto-Number/System

Megan Ryzenga

I use the INDEX/MATCH formula across many of my sheets for many years. This is the first year I've encountered this issue: my formula is indexing information referencing an auto-number system column type. We generate 3-digit "reference IDs" in this auto-number column to make it easy to index information for that respective row in the source sheet throughout multiple sheets. This year, we started the auto-number system with "001". What I'm finding is any "reference ID" that is above 099 results in a #NO MATCH error for my index/match formulas, i.e. "100" will populate "#NO MATCH". However, if I put an apostrophe before the "100", the formula works and pulls exactly what I need.

At first, I thought this was because Smartsheet did not recognize "100" as a text/string (similar to how Excel will remove zeros at the beginning of a number string unless the user includes an apostrophe in the beginning), but I'm finding this same #NO MATCH result for "reference IDs" that are in the 200s. Help! I cannot begin to explain how this can be a huge inconvenience to explain to my team, which all have varying levels of knowledge and understanding to why this is happening.

Here is the resulting error when I do not include the apostrophe:

And the correct result when I do include the apostrophe:

And this is the formula I am using, which I do not change at all to get either aforementioned results:

Jeff Reisman

With your AutoNumber columns you may notice that sometimes the values align on the left side of the column, and other times they align on the right. When they align on the left, it's because they are text. When they align on the right, it's because they are integers. When you create an AutoNumber column, if you give it a prefix or leading zeroes, it will be text. If you don't specify a prefix or leading zeroes, it will be integer values. I know this is how it works because I just did it over and over, and that's what happened every time. You will never be able to obtain a MATCH in Smartsheet between a text value and an integer value.

Here you see my formula, same syntax as yours, matches and works:

When I copy this AutoNum value to another cell, it copies as a number, not text.

I got rid of the AutoAuto column, saved, and made another called AutoText with leading zeroes. The values created are text, you can see when you copy one into another cell:

No match between my values...

...unless I change my value to text:

So the moral of the story is: on the sheets where it's working, the AutoNumber field and the field you are matching it to are the same type, either both text or both numbers. Where it is broken, it's due to the mismatch in data types.

Jeff Reisman

@Megan Ryzenga

I think you're onto the problem already. The system has to treat the AutoNumber as text because you have leading zeros, so when trying to evaluate those AutoNumber values against numeric, it will throw the no match error.

Try adding a helper column which finds the VALUE of the AutoNumber cells, then use that helper column in your INDEX/MATCH. That lets the system evaluate both by their numeric value only.

In your helper column:

=VALUE(Reference_ID@row)

then reference the helper column range instead of the Reference_ID range:

=INDEX({Event Name}, MATCH([Ref ID 1]@row, {Value of Reference ID}, 0))

Megan Ryzenga

@Jeff Reisman I appreciate you for taking the time to respond. However, the problem extends to numbers that do not have leading zeros. Also, we have been using auto-number columns for reference IDs since 2020 without issue until recently. This is also only an issue with certain sheets; I'm using INDEX/MATCH on other sheets referencing auto-number columns with and without leading zeros that do not produce a #NO MATCH error.

Jeff Reisman

With your AutoNumber columns you may notice that sometimes the values align on the left side of the column, and other times they align on the right. When they align on the left, it's because they are text. When they align on the right, it's because they are integers. When you create an AutoNumber column, if you give it a prefix or leading zeroes, it will be text. If you don't specify a prefix or leading zeroes, it will be integer values. I know this is how it works because I just did it over and over, and that's what happened every time. You will never be able to obtain a MATCH in Smartsheet between a text value and an integer value.

Here you see my formula, same syntax as yours, matches and works:

When I copy this AutoNum value to another cell, it copies as a number, not text.

I got rid of the AutoAuto column, saved, and made another called AutoText with leading zeroes. The values created are text, you can see when you copy one into another cell:

No match between my values...

...unless I change my value to text:

So the moral of the story is: on the sheets where it's working, the AutoNumber field and the field you are matching it to are the same type, either both text or both numbers. Where it is broken, it's due to the mismatch in data types.

Megan Ryzenga

@Jeff Reisman I appreciate it, Jeff! I will go into my core sheet and alter the auto-number column to remove the leading 00s. Cheers!