Add Values to Each Dropdown Value

Pestomania

Hello,

We have a Supplier Dropdown with the name of each of our suppliers. I would like to create a formula that takes each of those values and goes out to another table (Supplier Directory) and returns the City, State, and Country of each of those suppliers.

I have the following formulas that look up the city, state, country, and join all of this together in a concatenated statement. It works perfect whenever there is only one supplier name, how can I make it work when there are a lot of them in the dropdown "Supplier Impacted".

City: =INDEX({Supplier Directory City}, MATCH([Supplier Impacted]@row , {Supplier Directory Supplier Name}, 0))

State: =INDEX({Supplier Directory State}, MATCH([Supplier Impacted]@row , {Supplier Directory Supplier Name}, 0))

Country: =INDEX({Supplier Directory Country Name}, MATCH([Supplier Impacted]@row , {Supplier Directory Supplier Name}, 0))

Statement: =[Supplier Impacted]@row + ": " + JOIN([Column22]@row :[Column23]@row , ", ") + " " + [Column24]@row

Philip Robbins

@Pestomania you won't be able to do this using a single column formula. If you know the maximum number of suppliers that could be selected, you would then need to create a several sets of helper columns, plus a final column to rejoin everything.

The first set of helper columns would need to FIND the space characters in the multi-select column, returning the character position of the space. You can chain these so each starts its search based on the position returned by the previous column. That way you can pick out all the space positions.

Then, in the next set of helpers, you would use the MID function to pull out the supplier name based on the previous position and the length of the supplier name (current position minus previous position). In these columns, you could also include your JOIN lookup.

Then, to pull them back into a single column, you would use the JOIN function with CHAR(10) as the delimiter.

It's a little complex, but it does work.