Return Multiple Values from a Lookup of Multiple Values

AnnThompson

I have a training feedback form, and classes have multiple instructors. When the student picks multiple instructors, I need to look up the corresponding email for each instructor, usually only 2. you can see from my screen shot, I am getting an error if there is 2 to lookup, is this even possible or what is a recommended work around?

Paul Newcome

You would use a JOIN/COLLECT/HAS combo along the lines of

=JON(COLLECT({Email Addresses}, {Names}, HAS([Instructor Name]@row, @cell)), "; ")

NicoLHC

@AnnThompson are you data filed setup as contact or text?

Paul Newcome

You would use a JOIN/COLLECT/HAS combo along the lines of

=JON(COLLECT({Email Addresses}, {Names}, HAS([Instructor Name]@row, @cell)), "; ")

AnnThompson

Thanks @Paul Newcome, I tried it and it gave me blanks where there was multiple, it worked on the singles :) Am I missing anything?

NicoLHC

@AnnThompson are you data filed setup as contact or text?

AnnThompson

@NicoLHC It is set up as text.

Paul Newcome

This has worked many times for me. What happens if you put Peter in with more than one person since we can see we are definitely getting a match on Peter?

AnnThompson

I changed it to a contact field and boom, it worked! Thanks much @Paul Newcome and @NicoLHC !

Paul Newcome

That's odd that that would have been the issue. I use this same method to bring a string of email addresses into text/number columns frequently. Glad it is working for you now though.