index/match based upon partial description

Colemcgu

I have scoured the community and unable to find something that works. It comes close. but not quite.

The closest I have gotten is using this formula

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

However it returns the date for the first item for pig1 (1/23/24) and not the date for the "bricks" class (2/3/24).

Thank you in advance for your help. This is driving me crazy! Ugh!!

Training Record

Attendance records

jmyzk_cloudsmart_jp

Hi @Colemcgu

To get date data with more than one condition, one way to do that is using the COLLECT function. In the formula below, the COLLECT function returns a date range that has a partial description like "bricks and mortar" in {class} or {training} range in your reference name, and the email is the same as the {email} range in the training record sheet.

Then, the INDEX function gets the first item in the range.

=IFERROR(INDEX(COLLECT({completion date}, {class}, CONTAINS([partial description]@row, @cell), {email}, email@row), 1), "")

https://app.smartsheet.com/b/publish?EQBCT=875a64e430994b02bfdddda91eac82f7

https://app.smartsheet.com/b/publish?EQBCT=7299662252894e7ab1d9564e42bd388dACQ

jmyzk_cloudsmart_jp

Hi @Colemcgu

To get date data with more than one condition, one way to do that is using the COLLECT function. In the formula below, the COLLECT function returns a date range that has a partial description like "bricks and mortar" in {class} or {training} range in your reference name, and the email is the same as the {email} range in the training record sheet.

Then, the INDEX function gets the first item in the range.

=IFERROR(INDEX(COLLECT({completion date}, {class}, CONTAINS([partial description]@row, @cell), {email}, email@row), 1), "")

https://app.smartsheet.com/b/publish?EQBCT=875a64e430994b02bfdddda91eac82f7

https://app.smartsheet.com/b/publish?EQBCT=7299662252894e7ab1d9564e42bd388dACQ

Colemcgu

Thank you @jmyzk_cloudsmart_jp!! it worked beautifully! I had tried all variations of collect, match, has, and contains. Happy Monday I appreciate you!

jmyzk_cloudsmart_jp

Happy to help!😁