How to check if a string is matching 2 criteria from 2 column

Hello All, thank you for your help, I've managed it with VBA, as below:

Code in a module:

Function FindInteractions(medString As String, medsA As Range, medsB As Range) As String

Dim result As String

Dim i As Long

Dim medList As Variant

Dim medSet As Object, pairSet As Object

Set medSet = CreateObject("Scripting.Dictionary")

Set pairSet = CreateObject("Scripting.Dictionary")

' Step 1: Parse A string into lowercase, trimmed, unique medication list

medList = Split(LCase(medString), "-")

For i = LBound(medList) To UBound(medList)

Dim med As String

med = Trim(medList(i))

If Len(med) > 0 Then medSet(med) = True

Next i

' Step 2: Only check to the last used row of medsA and medsB (optimization)

Dim lastRow As Long

lastRow = Application.WorksheetFunction.Max( _

medsA.Worksheet.Cells(medsA.Worksheet.Rows.Count, medsA.Column).End(xlUp).Row, _

medsB.Worksheet.Cells(medsB.Worksheet.Rows.Count, medsB.Column).End(xlUp).Row)

' Step 3: Loop through interaction table

For i = 2 To lastRow ' assumes headers in row 1

Dim medA As String, medB As String

medA = LCase(Trim(medsA.Cells(i, 1).Value))

medB = LCase(Trim(medsB.Cells(i, 1).Value))

If Len(medA) > 0 And Len(medB) > 0 Then

If medSet.exists(medA) And medSet.exists(medB) Then

Dim combo As String

If medA < medB Then

combo = medA & "-" & medB

Else

combo = medB & "-" & medA

End If

If Not pairSet.exists(combo) Then pairSet(combo) = True

End If

End If

Next i

' Step 4: Build result string from unique interaction pairs

Dim key As Variant

For Each key In pairSet.Keys

If result <> "" Then result = result & "; "

result = result & key

Next key

FindInteractions = result

End Function

and the formula in the cell:

=FindInteractions(A2, B:B, C:C)

This will test any possible occurrence and list multiple instances in front of each cell in column A.

Many thanks to ChatGPT :) as well.

select * from Sheet1;

create temp table aa as

select f01 from Sheet1 f01;

create temp table bb as

select f03,f04 from Sheet1 f01 where f03<>'';

select f01, group_concat(f03||'-'||f04,';') from aa left join bb on instr(f01,f03)>0 and instr(f01,f04)>0 group by f01;

In this layout:

try

B2
=OR(REGEXTEST(A2;"."&$C$2:.$C$100&"."&$D$2:.$D$100&".*"))

to drag, or using BYROW to expand them:
=BYROW(A2:.A2000;LAMBDA(r;OR(REGEXTEST(r;"."&C2:.C100&"."&D2:.D100&".*"))))

return true if combination in C and D is contained in A without udf in VBA or ChatGPT.