VBA Find match in another tables row based on multi-column criteria, using dynamic variants

[davideff]

I'm trying to convert a static parameter function into one that can be used with multiple values. Im having a hard time with this. It's kind of hard to explain so let me get down to it.

I have a function that I use to see if a table has a match with another table, but validating against 2 columns. The parameters are static. Here is an example:

Function GetAddressOfKey(col1 AsString, val1 AsString, col2 AsString, val2 AsString, strTable AsString)AsString

Dim lst As ListObjectDim lr As ListRowDim strAddress AsStringDim strCandidate1 AsStringDim strCandidate2 AsString

strAddress =""

Set lst = ActiveSheet.ListObjects(strTable)

'iterate rowsForEach lr In lst.ListRows'get candidate values
strCandidate1 = Intersect(lr.Range, lst.ListColumns(col1).Range).Value
strCandidate2 = Intersect(lr.Range, lst.ListColumns(col2).Range).Value
'check against inputsIf strCandidate1 = val1 And strCandidate2 = val2 Then
strAddress = lst.Range.Cells(lr.Index +1,1).Address
'quit if we find a matchExitForEndIfNext lr

'return
GetAddressOfKey = strAddress

EndFunction

This is from a question I asked on StackOverflow

Trying to convert this to a universal function, I've come up with this so far:

Function CRITERIA(ParamArray values() As Variant) As Variant
    CRITERIA = values
End Function

Function MULTIMATCHEXISTS(args As Variant, ParamArray colms() As Variant) As Boolean

End Function

Where the user would define more than one criteria using the criteria function (i already have this set up for other reasons so I decided to use it for this circumstance).

Say we are in Table1 and want to search for a match in Table2:

   =MULTIMATCHEXISTS(CRITERIA(A2,A3,A4), Table2[Column1], Table2[Column2], Table2[Column3])

Where the MULTIMATCHEXISTS Function is as follows:

Function MULTIMATCHEXISTS(args As Variant, ParamArray colms() As Variant) As Boolean
    Dim argsCount As Long, colmnsCount As Long, cl As Long, a As Long
    argsCount = UBound(colmns) - LBound(colmns) + 1
    colmnsCount = UBound(args) - LBound(args) + 1


    Dim tbl As ListObject
    Dim ws As Worksheet
     
    Dim lr As ListRow
    Dim match_candidate As Variant
    Dim arg As Variant

    If argsCount <> colmnsCount Then
        'Array counts dont match, UDF will automatically give a #VALUE err
     
        Exit Function
    Else

        'Get the name of the table from a column provided, this function assumes a 1:1 Table ratio match, doesn't query with more than one table
        Set tbl = colmns(0).ListObject
        'Get worksheet name from table
        Set ws = ThisWorkbook.Sheets(tbl.Parent.Name)
     
        'Iterate through columns provided?
        For i = LBound(colmns) To UBound(colmns)

            'Get each value from that column
            For Each lr In tbl.ListRows
                match_candidate = Intersect(lr.Range, colmns(i)).Value
             
                'Iterate through arguments?
                For a = LBound(args) To UBound(args)
                    'Do all the args match with respective columns? ie: arg(0) matches colmns(0) value, arg(1) matches colmns(1) value etc
                    If match_candidate = args(a) Then
                        Debug.Print "it's a match for" & args(a)
                 
                        'I can find matches, but I don't know how to short-circuit. Do ALL of them match at the same time?
                        MULTIMATCHEXISTS = True
                    End If
                Next a
                 
            Next lr

        Next i

    End If

   MULTIMATCHEXISTS = True
End Function

Basically I don't know how to validate if ALL the columns values match their respective argument value. All of them have to match or else it should return FALSE.

So arg(0) needs to equal the users colmns(0) value, arg(1) needs to equal colmns(1) and so forth for as many arguments they provide. I can check if there are matches, but I can't figure out how to check if ALL of them match

I know this doesn't make sense, at least for me it's hard to explain, so I included an attachment for reference. Thank you guys for your help