excel中自定义函数Mlookup一对多匹配函数代码

Mlookup(查找内容tj，查找区域rgs,返回值所在的列数L,第M个,连接字符串，默认为“,”)

1、查找内容：除了单个值外，还可以选取多个单元格，进行多条件查找。
2、查找区域: 同VLOOKUP
3、返回值的在列数L: 同VLOOKUP
4、第M个：值为1就返回第1个符合条件的，值为2就返回第2个符合条件的…当值为-1值时，返回最后1个符合条件的值，值为0时返回所有查找结果并用逗号连接

Function Mlookup(tj As Range, rgs As Range, L As Integer, M As Integer, Optional str As String = ",") As String
    Dim arr1, ARR2, Ls
    Dim r, k, i As Integer, S As String, Sr As String
    arr1 = tj.Value
    ARR2 = rgs.Value
    If VBA.IsArray(arr1) Then
        For Each r In arr1
            If r <> "" Then
                S = S & r
                Ls = Ls + 1
            End If
        Next r
    Else
        S = arr1
    End If
    If M > 0 Then '非查找最后一个
        For i = 1 To UBound(ARR2)
            Sr = ""
            If Ls > 1 Then
                For q = 1 To Ls
                    Sr = Sr & ARR2(i, q)
                Next q
            Else
                Sr = ARR2(i, 1)
            End If
            If Sr = S Then
                k = k + 1
                If k = M Then
                    Mlookup = ARR2(i, L)
                    Exit Function
                End If
            End If
        Next i
    ElseIf M = 0 Then '查找所有值
        For i = 1 To UBound(ARR2)
            Sr = ""
            If Ls > 1 Then
                For q = 1 To Ls
                    Sr = Sr & ARR2(i, q)
                Next q
            Else
                Sr = ARR2(i, 1)
            End If
            If Sr = S Then
                Mlookup = Mlookup & str & ARR2(i, L)
            End If
        Next i
        Mlookup = Right(Mlookup, Len(Mlookup) - Len(str))
        Exit Function
    Else '查找最后一个
        For i = UBound(ARR2) To 1 Step -1
            Sr = ""
            If Ls > 1 Then
                For q = 1 To Ls
                    Sr = Sr & ARR2(i, q)
                Next q
            Else
                Sr = ARR2(i, 1)
            End If
            If Sr = S Then
                Mlookup = ARR2(i, L)
                Exit Function
            End If
        Next i
    End If
    Mlookup = ""
End Function