Compare Two Lookup Fields for Common Values

[Steve_Cantrell]

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!

[Jon_Shay]

Bumping this thread, this would be incredibly useful.

[kuovonne]

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.)

Array intersect formula Product Suggestions

I have 2 tables, and I need a result that shows only inputs that are the same in both tables. Hence, to compute the intersection of a number of arrays. array intersect ( array1, array2 [, array#… ] ) Returns an array containing all the values of array1 that are present in all the arguments. Where: array1 The array with master values to check. array2 An array to compare values against. … A variable list of arrays to compare. Any workarounds at this point?

[CharlesR]

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) 

Thanks