looking for an index match formula that contains part of a name

I have scoured the community and find things that are close but don't work for my scenario. I am trying to pull in the completion date for class attendance based upon matching email addresses for classes that contain a descriptive word. For example, what is the date that Pig 1 completed the Bricks and Mortar class without having to match the entire name of the class?

=IFERROR(IF(CONTAINS("bricks", {Training}), INDEX({Completion Date}, MATCH([Email Address]@row, {email}, 0))), "")

This formula works, however it returns the date for "building straw houses" instead of "bricks".

I have tried multiple variations and ideas from the community and keep coming up with the same. ugh!!

Tracking Sheet

Course Attendance Sheet

Find more posts tagged with

Accepted answers

KPH

In the case where "bricks" is part of the string, you can use your CONTAINS function in place of the text string "bricks". The syntax is

CONTAINS("bricks", @cell)

So your full formula would look like

=INDEX(COLLECT({Completion Date}, {email}, [Email Address]@row,{Training},CONTAINS("bricks", @cell)),1)

Colemcgu

All comments

KPH

It looks like your formula is saying IF the class contains the word Bricks then do the INDEX and MATCH on email address. Bricks is not included in your match so it will return the first row that matches on email. I think you would be better off with an INDEX COLLECT formula. This would allow you to collect the rows that contain "bricks" and the email address and then index those.

It would look something like this:

=INDEX(COLLECT({Completion Date}, {email}, [Email Address]@row,{Training},"bricks"),1)

KPH

In the case where "bricks" is part of the string, you can use your CONTAINS function in place of the text string "bricks". The syntax is

CONTAINS("bricks", @cell)

So your full formula would look like

=INDEX(COLLECT({Completion Date}, {email}, [Email Address]@row,{Training},CONTAINS("bricks", @cell)),1)

Colemcgu

This worked! Thank you. This has been driving me crazy!

Colemcgu

KPH

I’m please I was able 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.