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

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:

Find more posts tagged with

Sheets

Formulas

Solution Center

functionality

Accepted answers

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.

All comments

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

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:

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!

MPath

https://community.smartsheet.com/discussion/comment/318612#Comment_318612

Hi Jeff,

Could the above be the reason why I get incorrect values in one of my sheets?

When numbers are entered, it will come with a match

And if does not, it will return "No description"('Geen beschrijving' in Dutch,auto translate on in pics).

But when text is entered throught the form the that same field, it will return a incorrect match (there should be no match). It is also always the same value it returns.

The formula in "Omschr.BesteldArt.nr." column.

=IFERROR(INDEX({Omschr-ArtikelLeverancierslijst}; MATCH([Besteld art.nr]@row; {Artikel-ArtikelLeverancierslijst})); "Geen omschrijving")

Order Item no. = Besteld art.nr column, auto translate switched on for pics)

Screenshot of the articlelist of the (mis)match it returns.

Items 1 tp 2271 are actually like this in the field:

2271 for example, where 0942028 is '0942028

Thoughts? :S :) Thanks in advance!

Jeff Reisman

@MPath Sorry for the delay in response. I'm not working with Smartsheet any longer due to a layoff so I'm rarely on here now.

I see a few issues. I am assuming that the reference {Artikel-ArtikelLeverancierslijst} is pointing to this column:

and the reference {Omschr-ArtikelLeverancierslijst} is pointing to this column:

There's missing syntax in your INDEX/MATCH formula. You need a zero near the end as the search type for the MATCH function. This tells MATCH to find the exact match for the search value in the referenced range.

=IFERROR(INDEX({Omschr-ArtikelLeverancierslijst}; MATCH([Besteld art.nr]@row; {Artikel-ArtikelLeverancierslijst}; 0)); "Geen omschrijving")

Because you have numeric values stored as text, for the Ordered Item no. values, you may have to add a leading single quote to the values that start with zero, in order to properly match. So actually entering '0942028 in that column will properly match to NAPKINS DARK BLUE 33 X 33.

MPath

@Jeff Reisman Sorry to hear you are no longer part of the team. Hope you have or will find a cool new job.

Your response has worked like a charm. Thanks!

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.