index/match based upon partial description
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
Best Answer
-
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
Answers
-
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
-
Thank you @jmyzk_cloudsmart_jp!! it worked beautifully! I had tried all variations of collect, match, has, and contains. Happy Monday I appreciate you!
-
Happy to help!😁
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.6K Get Help
- 433 Global Discussions
- 136 Industry Talk
- 468 Announcements
- 4.9K Ideas & Feature Requests
- 143 Brandfolder
- 147 Just for fun
- 64 Community Job Board
- 466 Show & Tell
- 32 Member Spotlight
- 2 SmartStories
- 298 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!