Vlookup equivalent for dropdown list with two or more inputs, in Excel

Question

---

I have a lookup tab where I specified a region for each country of expertise (never mind the title of the column).

I have a data input tab with a dropdown where you can select more than one country. If there is one country selected, then there is a Vlookup that picks the corresponding region.

I would like to create a formula so that if more than one country is selected, then the corresponding regions appear, comma separated. So for the choice of "Algeria, Armenia, Barbados" I would like the output to be: "South Med, Caucasus, Caribbean".

If two countries of the same region are selected, then show the region only once. E.g. if the user selects Armenia, Azerbaijan from the multiple selection dropdown, then show only Caucasus in the region.

Thank you for the help!

Answer 1

Here is another way of doing this, also it seems like you are using Structured References aka Tables then the following formula should work provided you change the name of the tables accordingly with accordance to the following formula, using TEXTJOIN() and IF() with TEXTSPLIT()

---

=TEXTJOIN(", ",1,IF(TEXTSPLIT([@Country],", ",,1)=Expertisetbl[Country],Expertisetbl[Region],""))

---

Or, Use the following if applicable to your version of Excel:

---

=ARRAYTOTEXT(FILTER(Expertisetbl[Region],1-ISNA(XMATCH(Expertisetbl[Country],TEXTSPLIT([@Country],", "))),""))

---

Formulas will automatically filled down due to the Structured References as well as makesure to change the Table names as per your suit.

---

Here is reference of other ways posted earlier: Array formula to lookup a string of comma separated values in a range and return every value in another comma separated string

---

Answer 2

You can try this one by dragging down.

=TEXTJOIN(",",,VLOOKUP(TEXTSPLIT(E6,","),$B$2:$C$27,2,0))