Looking up multiple different values with VLOOKUP?

[DaanVisser]

Hi,

I am currently working on an Excel file which should automatically place email addresses in a column based on names in a different column. Similar to this:

To achieve this, the combinations of names and email addresses are given in a different sheet:

Using VLOOKUP, I have already achieved to automatically fill in the Email addresses column when a single attendee is specified. However, this method does not work when multiple attendees are specified. I'm having trouble coming up with a feasible solution  Specifying every attendee in a separate cell/column and merging them after using VLOOKUP might be a possibility, but this is not desirable with a large number of attendees. Does anyone have any suggestions to achieve this? Any help would be greatly appreciated! 

(I would like the email addresses to be in this format because I am creating a Power Automate flow that automatically creates Outlook meetings based on the Excel file)

Thanks in advance!

[#2 · Accepted solution]

@DaanVisser 

Let's say the sheet that lists the email addresses is named List.

In C2:

In Dutch:

=TEKST.COMBINEREN(";"; WAAR; X.ZOEKEN(TEKST.SPLITSEN(B2; ", "); List!A:A; List!B:B; ""))

In English:

=TEXTJOIN(";", TRUE, XLOOKUP(TEXTSPLIT(B2, ", "), List!A:A, List!B:B, ""))

[#3]

Yesss that works, thank you so much Hans! Way easier than expected :D