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

Best Answers


  • KPH
    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
    KPH ✭✭✭✭✭✭
    Answer ✓

    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
    Colemcgu ✭✭✭

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

  • KPH
    KPH ✭✭✭✭✭✭

    I’m please I was able to help!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!