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

Tags:

Answers

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!