Lookup contact info from another table

[AndreaR]

Hi there - I keep getting hung up on lookups and links and it's driving me bananas so it's time to phone a friend!

To generalize, I have a "Counterparty" table that lists all of the counterparties our organization has a contract with that includes their state and county (these are also lookups from another table which might be complicating matters). I have a second, "Local Office" table that is synced from an external base that lists every local county office by state that counterparties need to meet with as part of their contract. Each row is a different office and the columns are "state, county, office name, phone, address." Each state is naturally different, so, for example, New York has 10 rows/county offices and New Mexico 4. Super basic, right?

I want to link the two tables so that we can create an automation that will automatically inform the counterparty the name and contact information for their local county office from the sync'd Local Office table. 

I cannot figure it out, though. Essentially, I want to add a lookup, formula, automation, whatever in the Counterparty Table that essentially says something like, "Look up the counterparty's county in the Counterparty Table, and look up the county from the Local Office Table. If the counties are the same, give me the name and contact info in a new column in the Counterparty table." 

Please and thank you for your help!

[TheTimeSavingCo]

Hmm, I'm not sure I'm following so I threw something together.  Does this look right to you? 

Link to base

[AndreaR]

Wow, thank you so much for offering your expertise! I think what I'm looking for is in the first screenshot but I do like how you summarized the data in the third too!

Do you have to manually add the "Counties<>States" link in the Counterparties table? It looks like you will in order to make the look up work...

Appreciate your time!

[TheTimeSavingCo]

Ah, the table in the third screenshot is required to get all of it to work

---
re: Do you have to manually add the "Counties<>States" link in the Counterparties table? It looks like you will in order to make the look up work...

Nope, we can make an automation that'll trigger whenever the "County" and "State" fields are filled, and its action will be to paste the value from the "County + State" formula field into the linked field, which will help us auto populate everything.  I've set it up in the base linked above so you can review it!

You can also duplicate the entire base to your workspace to view the formulas