CODEWITHSUNDEEP
indexingexcel-formulatextjoinexcel-tables
Kobe2424
Kobe2424

Reputation: 169

TEXTJOIN of INDEX MATCH unique values if value on another table is contained in a cell

On cell B5 I'm trying to get a TEXTJOIN with delimiter "," of INDEX MATCH to the price range you see on Table B. Because cell A5 contains "Apple" then "$$$" is one of the values I need, also A5 contains "Banana" then "$$" is the second value i need. Finally cell A5 contains "Pineapple" but because "$$$" is already was selected because of apple then no need to add it again.

Any help will be much appreciated. enter image description here

What would I do if instead of 1 cell like A5, I will have multiple rows like this: enter image description here

What would I do if I have a SKU that has only some of the Fruits and I have to use the formula based on the SKU?

enter image description here

Upvotes: 2

Views: 281

Answers (1)

mark fitzpatrick
mark fitzpatrick

Reputation: 3320

With Excel 365, you can do:

=TEXTJOIN(",", 1, UNIQUE( XLOOKUP( FILTERXML( "<m><s>" & SUBSTITUTE( [@Fruits], "|", "</s><s>" ) & "</s></m>", "//s" ),
                                   TableB[Fruit], TableB[Price Range],0)) )

enter image description here

Upvotes: 2

Related Questions