Looking up multiple different values with VLOOKUP?

Copper Contributor



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 :sad: 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! :smile:


(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 Replies
best response confirmed by DaanVisser (Copper Contributor)


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

In C2:

In Dutch:


In English:

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

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