How do I return multiple continents for multiple countries?

Question

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 | Asia, Europe, Australia

Fanta | Korea, Japan, China | Asia

I have the corresponding countries to continents ready. But it seems like no formula works for this

hansvogelaar · Answer

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

jundiyaalhaqiqi · Answer

try this=TEXTJOIN(", ";TRUE;(UNIQUE(XLOOKUP(TEXTSPLIT(C3;", ");$F$3:$F$8;$G$3:$G$8;0);TRUE)))

Forum Discussion

How do I return multiple continents for multiple countries?

2 Replies

Resources