Actually, this is quite possible — and easy — to do entirely within Airtable. In fact, I just put together a base demonstrating this in under 5 minutes. (The demo base uses bogus UPS shipping codes instead of email addresses simply because I had access to a large number of fake UPS codes, but the principal is the same.)
The secret is to create a third table to which each record in both [Table 1] and [Table 2] is linked. This third table (which I named [Join]) contains only a single record, and that record contains only a single field (in this case, called {Name}). In {Name}, I entered a single period character ('.').
To both [Table 1] and [Table 2] I added a Formula field called {Link to Join} with the following configured formula:
'.'
— that is to say, a period character enclosed in quotes.
In both [Table 1] and [Table 2] I right-clicked on {Link to Join} and changed the field type from ‘Formula’ to ‘Link to another record’. I selected [Join] as the table to which to link. This caused each record in [Table 1] and [Table 2] to be linked to the record named '.' in [Join].
Finally, I created a Rollup field called {Duplicate Address} in [Table 2]. It used the links provided in {Link to Join} to roll up the field {Link to Table 1} using the aggregation function
IF(
FIND(Email, ARRAYJOIN(values)),
'✅',
BLANK()
)
(In other words, if the value of {Email} can be found among the email addresses rolled up from [Table 1], display :white_check_mark: , the White Heavy Check Mark emoji.)
I tested this by copying an email address from [Table 1] and pasting it into a random record in [Table 2]. Immediately, a check mark emoji appeared in {Duplicate Address}.
You can find my demonstration base here. Initially, I created it with 1,200 dummy records in both [Table 1] and [Table 2], but I’ve since trimmed each table to 400 records to stay within free plan limits.
Please let me know if my explanation of this base isn’t clear…
Edit: Replaced UPS codes with bogus email addresses; both courtesy Fake Name Generator.
