Pulling Email Address from Another Sheet but as a Contact

In Sheet 1, a user enters a course code (e.g., BUS 123) in one column. Smartsheet searches for that code in Sheet 2, finds the corresponding program leader's name and e-mail address, then puts them in two cells in Sheet 1.

Now Sheet 1 shows the course code in Column 2, the program leader's name is Column 3, and the email in Column 4. Columns 3 and 4 obviously contain formulas to search Sheet 2.

Column 1 is a drop down in which the user provides an update on the course development---On Track or Not on Track. 

I would like Smartsheet to sent an alert if the dropdown is changer to Not on Track. Problem is I can only use an email address in a column set as Contacts, and Contacts columns cannot contain formulas. So while I have the email address in Sheet 1, the alert cannot work.

Is there a way to assign the email address to a cell in Sheet 1 that is a Contact? I cannot manually select the names/emails from a dropdown because I want to automate the course information being put in Sheet 1 so user only has to enter course code.

Any ideas about how to automatically get that program leader's name from Sheet 2 into Sheet 1 so I can use the alerts feature when he Status column is set to Not on Task?