Index/Match based on partial data

I am trying to develop an index/match formula. However, Sheet A has a 3 digit code that a user enters. I am trying to match based on another sheet that has "CTC-XXXX" - with a 4 digit code. As of now, the leading X is a 0 (zero).

=INDEX({CTC Promo Inventory Management Item}, MATCH([Item#]@row, {CTC Promo Inventory Management Simp#}, 0))

This returns no match, obviously because it is trying to match "376" to "CTC-0376". How can i modify this to match based on "contains"?

Find more posts tagged with

Accepted answers

Christina09

Hi @Mtmoroni

In this case, you should use INDEX/CONTAIN formula

=INDEX(COLLECT({CTC Promo Inventory Management Item}, {CTC Promo Inventory Management Simp#}, CONTAINS([Item#]@row, @cell)), 1)

Christina09

@Mtmoroni

Use IFERROR for what to happen if there is an error message (i.e no match)

=IFERROR(IF([Item#]@row > 300, INDEX(COLLECT({CTC Promo Inventory Management Item}, {CTC Promo Inventory Management Item#}, CONTAINS([Item#]@row, @cell)), 1), "Incomplete item #"),"No such item #")

All comments

Christina09

Hi @Mtmoroni

In this case, you should use INDEX/CONTAIN formula

=INDEX(COLLECT({CTC Promo Inventory Management Item}, {CTC Promo Inventory Management Simp#}, CONTAINS([Item#]@row, @cell)), 1)

Mtmoroni

This works great! Thank you!!! I want to build upon it if I can!

INDEX(COLLECT({CTC Promo Inventory Management Item}, {CTC Promo Inventory Management Item#}, CONTAINS([Item#]@row, @cell)), 1)

That's the formula I went with, as i changed some column names around. If I enter a number that does not exist (ie, 700), it shows "#INVALID VALUE". Can I have it somehow output "No such item #" if there is no match?

I also ran into if someone typed "37" it took the first item with a 37, which was 370. So since all item numbers are over 300, i added on:

=IF([Item#]@row > 300, INDEX(COLLECT({CTC Promo Inventory Management Item}, {CTC Promo Inventory Management Item#}, CONTAINS([Item#]@row, @cell)), 1), "Incomplete item #")

Christina09

@Mtmoroni

Use IFERROR for what to happen if there is an error message (i.e no match)

=IFERROR(IF([Item#]@row > 300, INDEX(COLLECT({CTC Promo Inventory Management Item}, {CTC Promo Inventory Management Item#}, CONTAINS([Item#]@row, @cell)), 1), "Incomplete item #"),"No such item #")

Mtmoroni

Thank you!!! That worked perfectly!

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.