Index/Match Formula Behavior

Hello,

I'm encountering an issue with the INDEX/MATCH function. When the value in the cell being match does not find a match and is recognized as text rather than a number, the formula does not return 'no match' as expected. Instead, it displays the last entry in the reference range.

Formula in cell Category Index/Match

=IFERROR(INDEX({Category}, MATCH([Equipment_ID]@row, {ID})), "The issue was reported via the generic form without specifying equipment ID")

Picture1.jpg

The reference range

Picture2.jpg

Could you please assist me in resolving this issue?

Find more posts tagged with

Formulas

References

INDEX

MATCH

Accepted answers

Paul Newcome

Try specifying an exact match in the MATCH function:

=IFERROR(INDEX({Category}, MATCH([Equipment_ID]@row, {ID}, 0)), "The issue was reported via the generic form without specifying equipment ID")

All comments

Paul Newcome

Try specifying an exact match in the MATCH function:

=IFERROR(INDEX({Category}, MATCH([Equipment_ID]@row, {ID}, 0)), "The issue was reported via the generic form without specifying equipment ID")

Anna Makhina

Thank you! Now it works

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