Forum Discussion
How do I return multiple continents for multiple countries?
I have a list of soft drinks being sold in different countries. I want to return the continents for them. i.e. Soft drink | Countries | Continents Coca cola | China, Denmark, Italy, Australia | ...
With a list like this on a sheet named Countries:
And with a setup like this on another sheet:
Use
=BYROW(B2:B5, LAMBDA(r, IFERROR(TEXTJOIN(", ", TRUE, SORT(UNIQUE(XLOOKUP(TEXTSPLIT(r, ", "), Countries!A2:A13, Countries!B2:B13), TRUE), , , TRUE)), "")))