Matching Values from two multiselect columns

Question

Hello,

I have in an intake sheet two multi-select columns - Carriers and ASLOBs. In another reference sheet I have a matrix where I have combinations of Carriers and ASLOBs with a matching Operating Company.

What I am trying to active is:

Match first value from Carriers column to first value from the ASLOBs column and then look for the combination in the reference sheet to get the value of respective Operating Company and so on.

I'm collecting the Operating companies based on these combinations back into intake sheet in a new multi-select column.

The following formula I've so far works only on single select values.

=JOIN(COLLECT({OPCO - Carrier - LOB Matrix || Operating Company}, {OPCO - Carrier - LOB Matrix || Carrier}, HAS(@cell, Carriers@row), {OPCO - Carrier - LOB Matrix || ASLOB}, HAS(@cell, ASLOBs@row)), ",")

ameya · Answer

I'll create an example and share screenshots. Thank you Paul.

Paul Newcome · Answer

Are you able to provide some screenshots (with mock data if needed) for context?

Try switching the HAS functions syntax around

HAS(Carriers@row, @cell)

HAS(ASLOBs@row, @cell)