Welcome to the community, @Phil_Waring!
It sounds like you’re coming to Airtable from the relational database world. Airtable is not a full-blown relational database system. It is a spreadsheet program with some fantastic database features built into it, but it is not a complete relational database system.
In Airtable, you can’t automatically link tables based on field values nor based on formulas. Airtable requires the human to MANUALLY & EXPLICITLY link records to one another.
This is done by creating a “linked record” field which points to the other table, and then you can MANUALLY choose which record you want to link it to in the other table.
In Airtable, you are always linking to the entire record as a whole based on its PRIMARY FIELD VALUE, never based on any other field values.
And this is always done MANUALLY in Airtable.
There are only 3 exceptions when this linking is done AUTOMATICALLY for you:
Exception #1: If you convert a text field to a “linked record” field, that is one of the few times when Airtable will “automatically” link records between tables for you — as long as the contents of the converted text field match the PRIMARY FIELD VALUE in the other table. But this is just a “one time only” automatic linking during the field conversion process. (Airtable will automatically create records in that other table if your converted value doesn’t match any existing records.)
Exception #2: If you paste (or type) data into a “linked record field” and the value you just pasted exactly matches A PRIMARY FIELD VALUE in the other table. Then, an automatic link will be created to that other record. (Airtable will automatically create records in that other table if your pasted value doesn’t match any existing records.)
Exception #3: If you group all of your records by a “linked record field”, adding a new record to the group will automatically populate the link field with the same value as the group, thus linking you to the same record as the other records in your group.
Hope this helps! If this answers your question, could you please mark this comment as the solution to your question? This will help other people who have a similar question.