Unique last match udf

reggieneo

New Member
Joined
Jun 27, 2017
Messages
26
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:

Excel Facts

Bring active cell back into view
Start at A1 and select to A9999 while writing a formula, you can't see A1 anymore. Press Ctrl+Backspace to bring active cell into view.
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 :))
Add an extra column to your data table
- 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:
Upvote 0

Forum statistics

Threads
1,223,908
Messages
6,175,306
Members
452,633
Latest member
DougMo

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top