Compare Two Lookup Fields for Common Values

Topic Labels: Formulas
2785 3
Showing results for 
Search instead for 
Did you mean: 
4 - Data Explorer
4 - Data Explorer

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.

3 Replies 3
5 - Automation Enthusiast
5 - Automation Enthusiast

Bumping this thread, this would be incredibly useful.

There is a product suggestion for this feature. Unfortunately the suggestion is about three years old and does not appear to be implemented yet.

However, once the new Scripting block comes out, you might be able to implement with a script. (I don’t know as I haven’t been privy to the beta.)

4 - Data Explorer
4 - Data Explorer

I'm experiencing the same issue as @Steve_Cantrell. Since the post is fairly old I'm wondering if anyone as the solution now would it be thanks to a formula, an automation or a script. To be precise, I'm struggling with the same issue but would love to find a solution to get common values comparing several lookup fields (not only 2)