Using Vlookup where search value contains multiple values (column is a drop down)

Dallas G

I have a main sheet where i want to select "Plants for Consideration", then after selecting a production plant the emails of the people involved will populate in another cell.

I've created a separate smartsheet to reference which has the column on the far left "Plants for Consideration" and then in another column (column 11) i have all of the emails of the personnel i would need to reach out to.

This is the formula:

=IFERROR(VLOOKUP([Plants for Consideration]@row, {Temp Plant Contact Details_6-17-2024 Range 1}, 11, true), "")

The problem being, the "Plants for Consideration" column in the main sheet has multiple options. I want to be able to select multiple plants and have all of the emails be referenced.

Is this possible?

Thank you in advance,

Brian_Richardson

You can use HAS for this:

=JOIN(COLLECT({Emails}, {Plants}, HAS([Plants for Consideration]@row, @cell)), ";")

Where {Emails} is a cross-sheet reference for the Plant Contact Email(s) column, and {Plants} is a cross-sheet reference for the Plants for Consideration column.

Basically this does a "reverse lookup". The formula runs like this:

1. COLLECT looks at each row of your reference sheet, in the Plants for Consideration column
2. HAS says "ok - for the Plants for Consideration cell that I'm on in the reference sheet (this is the @cell part), does it show the same word as one of the selections in the Plants for Consideration cell on my row in the first sheet?" HAS specifically is used when you have a multiple-selection dropdown list that you're trying to match to…it will match if any of the selections match.
3. If the plant name on the reference sheet matches any of the plant names selected on the first sheet, COLLECT will pick up the email addresses from that row on the reference sheet and add them to a temporary array (a list).
4. COLLECT then moves on to the next reference sheet row and repeats the process, all the way down the sheet.
5. JOIN then takes all the emails that COLLECT has "collected", and separates them with a ; and then posts them into the contacts cell on the first sheet.

Brian_Richardson

Couple of ways depending on what you want.

For super basic you can use DISTINCT

= JOIN (DISTINCT (range), ";" )

If you have other criteria you want to use to determine what to join, you can use JOIN(COLLECT for that. COLLECT is useful in a lot of ways :-).

= JOIN ( COLLECT ( range, range , <>"", range 2, criteria2 ), ";" )

Brian_Richardson

You can use HAS for this:

=JOIN(COLLECT({Emails}, {Plants}, HAS([Plants for Consideration]@row, @cell)), ";")

Where {Emails} is a cross-sheet reference for the Plant Contact Email(s) column, and {Plants} is a cross-sheet reference for the Plants for Consideration column.

Basically this does a "reverse lookup". The formula runs like this:

1. COLLECT looks at each row of your reference sheet, in the Plants for Consideration column
2. HAS says "ok - for the Plants for Consideration cell that I'm on in the reference sheet (this is the @cell part), does it show the same word as one of the selections in the Plants for Consideration cell on my row in the first sheet?" HAS specifically is used when you have a multiple-selection dropdown list that you're trying to match to…it will match if any of the selections match.
3. If the plant name on the reference sheet matches any of the plant names selected on the first sheet, COLLECT will pick up the email addresses from that row on the reference sheet and add them to a temporary array (a list).
4. COLLECT then moves on to the next reference sheet row and repeats the process, all the way down the sheet.
5. JOIN then takes all the emails that COLLECT has "collected", and separates them with a ; and then posts them into the contacts cell on the first sheet.

Dallas G

Wow, that worked. Thank you for such a quick reply. That is exactly what i was looking for!

Brian_Richardson

You're welcome!

Dallas G

on a somewhat similar topic. when it comes to just joining a number of emails into a single text column, but i have a couple columns that are blank.

is there some way i can modify the join() function.

I just want all the emails to be in a text list with a ";" delimiter between them and remove blanks.

Brian_Richardson

Couple of ways depending on what you want.

For super basic you can use DISTINCT

= JOIN (DISTINCT (range), ";" )

If you have other criteria you want to use to determine what to join, you can use JOIN(COLLECT for that. COLLECT is useful in a lot of ways :-).

= JOIN ( COLLECT ( range, range , <>"", range 2, criteria2 ), ";" )