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 2424 Downloads 997

VBA-Excel Components
ANmarAmdeen
719
Revisions

v2.0

Needs