CONTAINS version of INDEX, MATCH?
CONTAINS version of INDEX, MATCH?
The following formula works for me when I am trying to get the MN Description for a row when MN Ref # has only one Track ID.
=IF([Site]@row = "MN", INDEX({MN Description}, MATCH([Track ID]@row, {MN Ref #}, 0)))
My problem is that sometimes the MN Ref # will contain multiple Track IDs.
I am trying to update the formula so that it works if MN Ref # contains the Track ID I am looking for but keep getting errors and am stuck.
Answers
-
Try:
=IF([Site]@row = "MN", INDEX({MN Description}, MATCH(HAS(@cell, [Track ID]@row), {MN Ref #}, 0)))
Work or error?
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
This returns "NO MATCH". I think it is because I am searching for one Track ID in the column MN Ref # where each cell may contain multiple Track ID (each separated with a space). The HAS function seems to be looking for an exact match?
-
Hi Christine,
That will do it. HAS works with multi-select drop downs which is what I assumed you were using. Will CONTAINS work:
=IF([Site]@row = "MN", INDEX({MN Description}, MATCH(CONTAINS(@cell, [Track ID]@row), {MN Ref #}, 0)))
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
-
Mark,
That gives me #INVALID OPERATION
-
I ended up changing the column I was trying to use the Contain function on from Text to Multiple Drop Down. This allowed me to do the following which worked.
=IF([Site]@row = "MN", JOIN(COLLECT({MN Description}, {MN Ref #}, CONTAINS([Track ID]@row, @cell))))
Thanks for all your help Mark!
-
Glad you found a solution. Thank you for contributing to the Community.
Mark
I'm grateful for your "Vote Up" or "Insightful". Thank you for contributing to the Community.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.9K Get Help
- 439 Global Discussions
- 138 Industry Talk
- 471 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 68 Community Job Board
- 488 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!