Solved: How to populate a list of emails - Page 2

Danny_Frank · 04-13-2020 03:48 PM

Hello,

I am developing a scheduling application.

When looking at the spreadsheet I had been working in I was able to use the following formula to return a list of student email addresses (separated by commas) based on their names being listed in another cell (also separated by commas.)

=ARRAYFORMULA(TextJoin(",",1,IF(V18="",VLookup(Transpose(Trim(Split(V18,","))),Roster!A$2:E,3,0))))

Example: Dan, Joe, Jane in cell V18 would return Dan@icloud.com ,Joe@hotmail.com, Jane@gmail.com. The only problem with this approach is that I have to manually copy the formula into each row. Is there anyway that I can write an expression in appsheet that can do this for me?

Thank you all for your help!

Marc_Dillon

You have a Roster table that lists names and emails? I’ll assume so, and assume it looks like this:

Then you have a sheet with a Text/LongText type column in which you’ve manually written in Roster names, separate by commas?

We can get an email list by using this expression in another column:

SELECT( Roster[email] , CONTAINS( [_THISROW].[roster name list] , [Name] ) )

Might I also suggest using an EnumList type column in place of your comma-separated roster name list. This gives you easier input. Just set a valid_if expression as “Roster[Name]”. In which case, the above expression would become.

SELECT( Roster[email] , IN( [Name] , [_THISROW].[roster name list] ) )

View solution in original post