SOLVED

Looking up multiple different values with VLOOKUP?

Copper Contributor

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:

 

DaanVisser_1-1692266030418.png

 

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

 

DaanVisser_2-1692266132404.png

 

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

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

Accepted Solutions
best response confirmed by DaanVisser (Copper Contributor)
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, ""))

View solution in original post