Index/Match

Jbob

Hello

when matching between two smartsheets using this formula

=INDEX({Livestock Transferred on Range 3}, MATCH(nlisid@row , {Livestock Transferred on Range 4}))

it sometimes returns #NO MATCH even when there is the same exact data in nlisid to match to, they are both dropdowns.

Out of 909 lines 59 are not working correctly.

appreciate any suggestions!

Thank you

Find more posts tagged with

Formulas

Accepted answers

Paul Newcome

Try using a zero for the third portion of your MATCH to specify an exact match. Not using this when looking for exact matches can sometimes throw that error.

=INDEX({Livestock Transferred on Range 3}, MATCH(nlisid@row, {Livestock Transferred on Range 4}, 0))

Jbob

Perfect!

Thank you🤩

All comments

LeelaLodhi03

Hello @Jbob ,

This usually happens because some values look the same but are not exactly the same in the background. Small differences like extra spaces, different letter case, or dropdown options being created separately in each sheet can cause #NO MATCH. I recommend checking for hidden spaces and making sure both dropdowns use the same standardized list of values.

Hope this helps.

Paul Newcome

Try using a zero for the third portion of your MATCH to specify an exact match. Not using this when looking for exact matches can sometimes throw that error.

=INDEX({Livestock Transferred on Range 3}, MATCH(nlisid@row, {Livestock Transferred on Range 4}, 0))

Jbob

Perfect!

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.