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

Question

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.

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

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?

Answer 1

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)) )