Forum Discussion
Looking up multiple different values with VLOOKUP?
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!
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, ""))
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, ""))
- Yesss that works, thank you so much Hans! Way easier than expected 😄
