# Unique last match udf

#### reggieneo

##### New Member
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
End Function``````

Last edited:

### Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use \$ signs: \$V\$2:\$Z\$99 will always point to V2:Z99, even after copying

#### Yongle

##### Well-known Member
I am trying to understand what you want as your "end" result
Q1. Do you simply want to replace the formula in column J with its value whenever there is a valid match?

Q2. Does this new column return correct matches and the values required to be "fixed" ?
(this is a temporary column, and only for my benefit )
- insert this formula in row3 and copy down (amend 3 if data starts in a different row)
=IF(AND(A3=\$B\$2,C3=\$C\$2),J3,"")

Let me know and I will amend the VBA for you

thanks

Last edited:

Replies
6
Views
354
Replies
16
Views
195
Replies
7
Views
74
Replies
41
Views
834
Replies
4
Views
307

1,109,337
Messages
5,528,113
Members
409,802
Latest member
joeino

### This Week's Hot Topics

• Change military grades into rank
Afternoon all Need help with formula that will change military rank (i.e. 1, 2, 3 into Amn, A1C, SrA). Running IF formula that does not work...
• VBA COUNTIF SOLUTION
Hi The following are the errors spread across the several columns from E to Q ie. 13 columns across several sheets with more than 500 rows per...
• INSERT ROW WITH SPECIFIS TEXT IN A COLUMN
Hi All! How can identify that that the row to be inserted has to be inserted before 1st row with specific text in column F. If I record the...