2

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

Kobe2424
  • 147
  • 7

1 Answers1

2

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

mark fitzpatrick
  • 3,162
  • 2
  • 11
  • 23
  • If it is truly a range (e.g. from $$ to $$$) then the answer is different and a little messy. It is also possible to do with with a lower version of Excel, but messy. – mark fitzpatrick Oct 03 '21 at 16:33
  • the formula above worked, amazing. thanks! – Kobe2424 Oct 03 '21 at 20:58
  • Mark I've edited my question to include a case where instead of 1 cell like A5, I will have multiple rows, would you know the solution to this as well? – Kobe2424 Oct 05 '21 at 15:59
  • Hi @Kobe2424 - sure, it's a simple change. You can simply remove the `@` from the formula so that it will evaluate the whole column. This would support concatenated entries mixed with single entries - like this: `=TEXTJOIN(",", 1, UNIQUE( XLOOKUP( FILTERXML( "" & SUBSTITUTE( [Fruits], "|", "" ) & "", "//s" ), TableB[Fruit], TableB[Price Range],0)) )`. If you will never have concatenated entries, then the formula can be a lot simpler - like this: `=TEXTJOIN(",", 1, UNIQUE( XLOOKUP( [Fruits], TableB[Fruit], TableB[Price Range],0)) )`. Let me know if that does not work. – mark fitzpatrick Oct 05 '21 at 17:45
  • 1
    Works. Thank you once again! – Kobe2424 Oct 05 '21 at 19:06
  • mark I've added one more scenario I will love to use your help once again. 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? (I've edited the original post with another image 3rd scenario) – Kobe2424 Oct 25 '21 at 05:21
  • Hey @Kobe2424! When I get a break, I can propose a way. Basically, I think it would be best to wrap `[Fruits]` in a FILTER that filters them according to each SKU, so it is probably `=TEXTJOIN(",", 1, UNIQUE( XLOOKUP( FILTER( [Fruits],[SKU] = [@SKU] ), TableB[Fruit], TableB[Price Range],0)) )`, but I have not tested it. – mark fitzpatrick Oct 25 '21 at 11:14
  • 1
    thank you mark once again. I opened a thread for this one and someone answered. I appreciate your time and effort – Kobe2424 Oct 25 '21 at 11:25