Hello All, I have managed to use nested index match to get the first match with multiple criteria but really struggled to get the last match until I found these article in MSDN from Hans Vogelaar (http://www.eileenslounge.com). this works well but only for one match only . I need to find not only the unique last match in J but in multiple column if X =A and Y =C . Appreciate all the help. Much Thanks.
Code:
Sub FindMatch1()
With Worksheets("CVerify")
.Range("J2").Value = "=FindMatch(B2,C2)"
.Range("J2").Value = .Range("J2").Value
End With End Sub
Function FindMatch(x As Variant, y As Variant)
Const FirstRow = 4
Dim LastRow, LastRow1 As Long
Dim CurRow As Long
With Worksheets("CVerify")
LastRow = .Range("B:C").Find(What:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
LastRow1 = .Range("B:C").Find(What:="*", SearchOrder:=xlByRows, _
SearchDirection:=xlPrevious).Row
For CurRow = LastRow To FirstRow Step -1
If .Range("A" & CurRow).Value = x And _
.Range("C" & CurRow).Value = y Then
FindMatch = .Range("J" & CurRow).Value 'And FindMatch = .Range("P" & CurRow).Value
Exit Function
End If
Next CurRow
End With
' If we get here, no match was found
FindMatch = "Not found"
End Function
Last edited: