MatchIF5

Searching for rows having 5 values in 5 columns
Like Match4, Match3 and others, but this one with 5 conditions.
Edit 2024-05-16: Updated version to accept wild card * in all Val1, Val2, Val3, Val4 and Val5 fields.
Also accepts <=, >=, <, or > to compare against numbers and <> to compare against strings and numbers

CodeFunctionName

What is this?

Public

Tested

Original Work

Function MatchIf5(Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Val5, Col5, Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1)
    MatchIf5 = Match5(Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Val5, Col5, WB, Shee, StartFromRow)
End Function
Function Match5(Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Val5, Col5, Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1)
    ' Searches for three cells in four 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
    Match5 = 0
    LastOne = MatchIf(Val1, Col1, WB, Shee, StartFromRow)
    Do
        If LastOne = 0 Then Exit Do
        Cond2 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value Like Val2
        Cond3 = Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value Like Val3
        Cond4 = Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value Like Val4
        Cond5 = Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value Like Val5

        If Left(Val2, 1) = " <" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) < Val(Mid(Val2, 2))
        If Left(Val3, 1) = " <" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) < Val(Mid(Val3, 2))
        If Left(Val4, 1) = " <" Then Cond4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) < Val(Mid(Val4, 2))
        If Left(Val5, 1) = " <" Then Cond5 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) < Val(Mid(Val5, 2))
        If Left(Val2, 1) = " >" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) > Val(Mid(Val2, 2))
        If Left(Val3, 1) = " >" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) > Val(Mid(Val3, 2))
        If Left(Val4, 1) = " >" Then Cond4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) > Val(Mid(Val4, 2))
        If Left(Val5, 1) = " >" Then Cond5 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) > Val(Mid(Val5, 2))
        If Left(Val2, 2) = " < >" Then Cond2 = Not Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value = Mid(Val2, 3)
        If Left(Val3, 2) = " < >" Then Cond3 = Not Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value = Mid(Val3, 3)
        If Left(Val4, 2) = " < >" Then Cond4 = Not Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value = Mid(Val4, 3)
        If Left(Val5, 2) = " < >" Then Cond5 = Not Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value = Mid(Val5, 3)
        If Left(Val2, 2) = " <=" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) <= Val(Mid(Val2, 3))
        If Left(Val3, 2) = " <=" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) <= Val(Mid(Val3, 3))
        If Left(Val4, 2) = " <=" Then Cond4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) <= Val(Mid(Val4, 3))
        If Left(Val5, 2) = " <=" Then Cond5 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) <= Val(Mid(Val5, 3))
        If Left(Val2, 2) = " >=" Then Cond2 = Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) >= Val(Mid(Val2, 3))
        If Left(Val3, 2) = " >=" Then Cond3 = Val(Workbooks(WB).Worksheets(Shee).Range(Col3 & LastOne).Value) >= Val(Mid(Val3, 3))
        If Left(Val4, 2) = " >=" Then Cond4 = Val(Workbooks(WB).Worksheets(Shee).Range(Col4 & LastOne).Value) >= Val(Mid(Val4, 3))
        If Left(Val5, 2) = " >=" Then Cond5 = Val(Workbooks(WB).Worksheets(Shee).Range(Col5 & LastOne).Value) >= Val(Mid(Val5, 3))

        If Cond2 And Cond3 And Cond4 And Cond5 Then
            Match5 = LastOne
            Exit Do
        End If
        DoEvents
        LastOne = MatchIf(Val1, Col1, WB, Shee, LastOne + 1)
    Loop
End Function

Val1, Col1, Val2, Col2, Val3, Col3, Val4, Col4, Val5, Col5, Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1