How to create a list of email ids (inside a single cell) for a list of names(inside another cell)

[rastameman]

This google sheet is an appsheet app database.

I have a list of (comma separated) employee names inside each cell in Column W , [People To Contact] of Table-1 (Activity Tracker). Each row is a separate job, and column W shows the people to be contacted for these jobs. 

The [People To Contact] column has an Initial value = concatenate([CS Rep],",",[Creative Lead]), where it combines names from 2 other 'name' type columns.

I need to fill Column X in Table-1 , with the corresponding comma separated email ids of all the names in Col W, for each row.

An example of how I want the output is as below

Table-2(User Manager) shown in the image below has 2 columns for Employee names(Column C) and their corresponding emails(Column D). These have to be referred to for getting the email ids of each person mentioned in each cell of Table-1 Column W.

I need to fill Column X in TABLE-1(ACTIVITY TRACKER) , with the corresponding comma separated email ids of all the names in Col W, for each row.

Please help, Ive gonemad after seeing various vlookup, index/match , query videos, filter etc.

[Suvrutt_Gurjar · Accepted solution]

Thank you. I first request you to go through the below mentioned articles and posts

References between tables - AppSheet Help

Dereference expressions - AppSheet Help

Use Enum/EnumList (Base Type: REF) to De-Reference... - Google Cloud Community

One option is as follows. However such approaches depend on how they fit in your overall app design. Also the approach presumes that the comma separated lists as AppSheet list columns compatible or created through the AppSheet app.

1. Please make column[People to contact] in table 1 , "Activity Tracker" as enumlist  type with base type as Ref, referenced table name as "User Manager" and valid_if expression as User Manager[Key column of User Manager] table. 

2. You can then get the [Contact Email List] in the "Activity Tracker" with an expression 

[People to Contact][Email] 

3. Please make the column [Employee Name] as label of the "User Manager" table

Where [Email] is the column name in the User Manager table and [People to Contact] is the column in the "Activity Tracker" table.