SOLVED

# Looking up multiple different values with VLOOKUP?

Copper Contributor

# 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)

2 Replies
best response confirmed by DaanVisser (Copper Contributor)
Solution

# Re: Looking up multiple different values with VLOOKUP?

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

# Re: Looking up multiple different values with VLOOKUP?

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

# Re: Looking up multiple different values with VLOOKUP?

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