Formulas and Functions

Formulas and Functions

Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

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

Answers

  • Community Champion

    Hi

    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)

  • Community Champion
    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)

  • ✭✭✭

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

  • Community Champion

    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!

Trending in Formulas and Functions