Match3

Matching three values at the same time in three columns
Just like Match the function, but with three cells instead of 1
Searches for three values in three columns and return the row number if all found.
Update 2020-06-23: Fix an issue in Cond2


Public

Tested

My Own Work
Function Match3(Val1, Col1, Val2, Col2, Val3, Col3, Optional WB = "This", Optional Shee = "Active", _
    Optional StartFromRow = 1)
    ' Searches for three cells in three columns and return the row number if all found
    If WB = "This" Then WB = ThisWorkbook.Name
    If WB = "Active" Then WB = ActiveWorkbook.Name
    If Shee = "Active" Then Shee = ActiveSheet.Name
    Match3 = 0
    LastOne = MatchIf(Val1, Col1, WB, Shee, StartFromRow)
    Do
        If LastOne = 0 Then Exit Do
        Cond1 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value = Val2
        Cond2 = Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value = Val3
        If TypeName(Val2) <> TypeName(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) Then _
            Cond1 = CStr(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) = CStr(Val2)
        If TypeName(Val3) <> TypeName(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) Then _
            Cond2 = CStr(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) = CStr(Val3)
        If Cond1 And Cond2 Then
            Match3 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Row
            Exit Do
        End If
        DoEvents
        LastOne = MatchIf(Val1, Col1, WB, Shee, LastOne + 1)
    Loop
End Function

Val1, Col1, Val2, Col2, Val3, Col3, Optional WB, Optional Shee, Optional StartFromRow

Views 2771 Downloads 1090

VBA-Excel Components
ANmarAmdeen
780
Revisions

v2.0

Needs