Welcome to the Airtable community!
There a ways to automate this, but it might not be easy.
Here are some questions to consider.
- How do you want to handle the backlog of records to process?
- How do you want to handle new data that comes in?
- Are the email addresses in the primary field of either table?
- Does the case of the email address matter? Should email addresses that are the same except for upper/lower case be treated as the same email address?
- Is each email in Table 2 guaranteed to appear exactly one time in Table 1?
One way to do this is to have a button script to do the comparisons and set the field values. Then have another automation script to watch for new records or change to email addresses and set the field value. On the other hand, this requires scripting knowledge.
If each email address in [Table 2] appears exactly one time in [Table 1], it might be trigger an automation from the record in [Table 2] and use a “find actions” automation action to update the record in [Table 1]. However, if there are situations where the email does not exist in [Table 1] or there are multiple matches in [Table 1], the automation won’t work.