Index, Match formula returning some, not all values

Formula was flowing correctly, but some change seems to have partially broken the pull but I can't figure out what/how.

"Project" column dropdown is linked to a Master sheet.

Budget Owner column contains the below formula which should look for the cell value in the Project/Cost Center/IO column within the P+CC+IO column of the Master sheet and return the Budget Owner.

=INDEX({Master List - CC/IO Budget Owners - Owner}, MATCH([Project/Cost Center/IO]@row, {Master List - CC/IO Budget Owners - P+CC+IO}))

Some projects are pulling correctly and some are returning "#NO MATCH". Below is a snap of the Master sheet. Flag indicates it's returning a "#NO MATCH" and P+CC+IO is a column formula that combines values of the project, cost center, and io columns.

I wouldn't think this is a formatting issue as the P+CC+IO column of the master is what is feeding the dropdown menu on the sheet giving the errors.

Only change I can think of making since yesterday (when it was working fine) was deleting a reference but it was is not used in the Master sheet or the two columns in question. I have deleted and manually re-entered all the values on the Master (same rows still error). I have change the Project/Cost Center/IO value in a blank row, check each dropdown option for the error, and it continues.

I'm stumped

Help😓

Find more posts tagged with

Formulas

Linked dropdown columns

Accepted answers

Paul Newcome

Try specifying an exact match with a zero in the third portion of the MATCH function:

=INDEX({Master List - CC/IO Budget Owners - Owner}, MATCH([Project/Cost Center/IO]@row, {Master List - CC/IO Budget Owners - P+CC+IO}, 0))

All comments

ccarriger

I would do INDEX COLLECT in this scenario, assuming both sheets have the P+CC+I+O column.

INDEX(COLLECT({Budget Owners}, {P+CC+I+O}, [P+CC+I+O]@row),1)

Jgorsich

That is a pretty odd problem, I'm not seeing anything obvious that would drive your error - it seems like it should work to me as well.

The best I can offer is what I would do to trouble shoot this if it was happening to me:

check the assumption that it is working correctly on the lines that AREN'T #NO MATCH. - instead of pulling from a column with lots of duplicates (your contact column), index through a column of unique values instead to see if it is pulling the right value (vs one that looks the same). This could just be a column of integers (probably safest) or it could be your P+CC+ID column (easiest - but if you've got duplicates there that are causing the issue you might not notice).
check to see if you can figure out WHAT is driving the error - it could be a new Table issue with the dynamic dropdowns, or it could be a data driven issue with the data you are using.
1. To see if it is Table: Pick your first 5 or 10 values, something that is hitting a mix of finding a match and not, and see if it works if you paste them in directly into a non-dynamic list setup instead of a dynamic list. Then repeat again after switching to Grid view instead of Table view.
2. To see if it is an issue with the data: switch to easier data - rather than feeding from and trying to match a fairly complex P+CC+ID column (pulled together via a formula with lots of atypical characters) try the exact same setup but fill your P+CC+ID column with just increasing integers instead.
If it is 2a, reach out to Smartsheet and report a bug…
If it is 2b, start isolating WHAT in your data is throwing the issue. I don't see anything obvious in your setup, but I've seen similar problems arise from numeric LOOKING data (actually a string though) being treated as a number SOMETIMES and having leading zeros stripped out, resulting in a #NO MATCH even when the information entered in both cells was clearly identical (when processing they BECAME unidentical, but that was invisible to the user). I found a way around it by adding a "k" (picked randomly) in front of the numbers to force it not to happen. SOMETHING seems to not be traveling well when pulled into the dynamic dropdown - once you know WHAT, figuring out how to make it a better traveler becomes easier.

Good luck!

Paul Newcome

Try specifying an exact match with a zero in the third portion of the MATCH function:

=INDEX({Master List - CC/IO Budget Owners - Owner}, MATCH([Project/Cost Center/IO]@row, {Master List - CC/IO Budget Owners - P+CC+IO}, 0))

E.Lengermann

@Paul Newcome that did it! thank you!

Paul Newcome

Happy to help. 👍️

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.