JOIN/INDEX MATCH HELP

Katrina Savage

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

jmyzk_cloudsmart_jp

HI @Katrina Savage

You could use a formula something along the lines of...

=JOIN(COLLECT({SUB PORTFOLIIO}, {PRODUCT}, CONTAINS(@cell, [PRODUCTS OTHER]@row)), ", ")

https://app.smartsheet.com/b/publish?EQBCT=18a7eef22ab8407fbf960b7e35479836