Match2

Matching by two values at the same time in two columns
Just like Match the function, but with two cells instead of 1
Searches for two values in two columns and return the row number if both found

Has custom condition to check if length of Val2 is greater than a number instead of matching full string (using Val2 with LenGT).
Also, if Val2 starts with LenGE to test if Val2 is greater than or equal a number.

CodeFunctionName
What is this?

Public

Tested

Original Work
Function Match2(Val1, Col1, Val2, Col2, Optional WB = "This", Optional Shee = "Active", Optional StartFromRow = 1)
If WB = "This" Then WB = ThisWorkbook.Name
If WB = "Active" Then WB = ActiveWorkbook.Name
If Shee = "Active" Then Shee = ActiveSheet.Name
Match2 = 0
LastOne = MatchIf(Val1, Col1, WB, Shee, StartFromRow)
Do
If LastOne = 0 Then Exit Do
If IsNumeric(Val2) Then
If Val(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) = Val(Val2) Then Same1 = 1
Else
If UCase(Left(Val2, 6)) = "=LENGT" Then
If Len(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) > Val(Mid(Val2, 7)) Then Same1 = 1
ElseIf UCase(Left(Val2, 6)) = "=LENGE" Then
If Len(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) >= Val(Mid(Val2, 7)) Then Same1 = 1
Else
If Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value = Val2 Or _
CStr(Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Value) = CStr(Val2) Then Same1 = 1
End If
End If
If Same1 = 1 Then
Match2 = Workbooks(WB).Worksheets(Shee).Range(Col2 & LastOne).Row
Exit Do
End If
LastOne = MatchIf(Val1, Col1, WB, Shee, LastOne + 1)
Loop
End Function

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

Match2("ANmar", "A", "LenGT32", "B", ,"DBSheet")
To return the row number if column A is ANmar and length of value in column B is greater than 32

Views 3,125

Downloads 1,245

CodeID
DB ID