Formula for Looking Up Properties of Linked Items

[Alex_Rosenfeld]

Let’s say I have two tables in a workspace.

Table 1 has two columns. The first column is a list of vendors. The second column is a checkbox used to indicate whether the supplier is in-state.

Table 2 has two columns. The first column is a list of buyers. The second column is linked to Table 1’s vendors, and shows for each buyer which vendor or vendors they do business with. (Some buyers might do business with only one vendor, while some buyers might do business with multiple vendors.)

What I’d like to do is add a third column to Table 2, which indicates whether one or more of the vendors a buyer is doing business with is in-state, per the second column in Table 1. What formula or other method would I use to accomplish this?

[JonathanBowen]

HI @Alex_Rosenfeld - You need a look-up field:

However, this is only going to work for you if you have one vendor per buyer line. Once you add two (with in and out statuses) it is going to be hard to tell which one is which. Same with 3 or more vendors:

Generally, when you’ve got a many-to-many relationship (a buyer can have many vendors and a vendor can belong to many buyers), I’d recommend a “join table”, buyer-vendor:

but without knowing what you are trying to get to it is hard to say if this is a good idea for your base.

JB

[Alex_Rosenfeld]

Thanks for your response, @JonathanBowen. I’m not overly familiar with join tables, but based on what you’re describing, that seems like it would be way too time-intensive and would make the tables a lot tougher for folks to digest. Is there any way to accomplish what I’m describing with how my tables are currently set up? If not, that seems like a shortcoming with Airtable, as this would be doable via formulas in, say, Excel.