I’m trying to find the correct syntax for a formula that will compare two lookup fields, each with multiple values, to find the common values. I see similar questions posted but don’t seem to find anything that fits what I’m trying to accomplish, so any help would be appreciated.

Here’s the scenario:

I have three tables: Vendor Database, Product List and States. The Vendor Database has a Lookup Field for the Product List and a Lookup Field for States, and typically has multiple values in each field, since many of my vendors have several items available in a few states in the U.S. I have another table that includes all my incoming requests, and my goal is to see which vendors have service in the specific state requested.

Here’s what I have so far:

In my Workflow table where I have views for requests, I have a Lookup Field for Vendors with Services and another for Vendors in State and the results are perfect in those two fields. I want to compare the two to find common values that will show in a separate Suggested Vendors field (see screenshot). In my example Windstream, Cogent and CenturyLink would be the Suggested Vendors.

I’ve tried IF-FIND formulas and added &"" based on a post related to string values or arrays (?) and it seems like I’m close, but I may be overthinking what I need to do. This is obviously a non-working formula and may be incomplete, but it seems like it should be in the ballpark!

IF(FIND({Vendors with Services}&"",{Vendors in State}&""),{Vendor Company Name}&"",0)

Airtable%20Lookup%20Screenshot|690x112 :

If anyone has questions or a potential solution, please let me know.

Thanks!