JOIN/INDEX MATCH HELP
Hi all,
Is it possible to return multiple values in to a cell based on a match in a cell which may have multiple values?
I have 2 sheets - one is my master tracker. I am trying to autopopulate the sub-portfolio Column based on what product or products are selected in the PRODUCTS - OTHER which is a multidropdown column.
I can get a formula to work when there is only one product therefore needing only one portfolio but when more than one product the result is #nomatch.
The formula i am using is: =JOIN(INDEX({EMEA PRODUCT_PORTFOLIO_PLATFORM SUBPORTFOLIO}, MATCH([PRODUCTS OTHER]11, {EMEA PRODUCT_PORTFOLIO_PLATFORM_indexmatch PRODUCT}, 0)), ", ")
Here is screenshot from master tracker:
Here is sheet that I was using for index match but the issue being I cant do multiple value return with it;
My other thought is that I could have two products from the same sub-portfolio and therefore having the same subportfolio mentioned twice, is it also possible to do the above but avoid duplication - do I give up and just have a manual entry :-D??
Thanks,
Katrina
Answers
-
You could use a formula something along the lines of...
=JOIN(COLLECT({SUB PORTFOLIIO}, {PRODUCT}, CONTAINS(@cell, [PRODUCTS OTHER]@row)), ", ")
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 64.5K Get Help
- 424 Global Discussions
- 136 Industry Talk
- 465 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 146 Just for fun
- 63 Community Job Board
- 465 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 301 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!