CODEWITHSUNDEEP
excelvbaif-statementnestedvlookup
Abhiously
Abhiously

Reputation: 11

Running a nested If/Match

I have 4 columns of data. They are two separate columns of First Name/Last Name columns copied and pasted together.

What I want to do is run a match on the last names and IF they are equal, run a match on the first names.

The column ranges are dynamic which is why running a CONCATENATE and VLOOKUP formula works, but if I can get something less involved it'd be great.

   A       B        C      D      E
1 Last  First   Last2   First2
2 Sharma  Abhi  Smith   Kevin
3 Philip  Matt  Smith   GEORGIA
4 Franc   Pete  John    Bon Jovi 
5 Arnold  Susan Jack    White
6 Mallo   Chad  Sharma  Katie
7 Daigle  Steve Sharma  Abhi

My thought is that starting in cell E2 it should return a match or not a match (in this case only Row 2 should return a match. Currently it's returning a match every time - which is definitely not right.

This is the code I've written so far

Sub matchFunction()

On Error Resume Next

Dim BW_Row As Long
Dim BW_Clm As Long
    Table1 = Sheet2.Range("F11:F16") ' Range of all appointments last name
    Table2 = Sheet2.Range("$G$11:$G$16") ' Range of all appointments first name
    Table3 = Sheet2.Range("$H$11:$H$16") ' Range of leads
    BW_Row = Sheet2.Range("J11").Row ' Change E column if it's a match
    BW_Clm = Sheet2.Range("J11").Column
    
        For Each c In Table1
            
            For Each d In Table2
                    
                If Application.Match(c, Table3, 0) <> "" Then
                    
                    If Application.Match(d, Table3, 0) <> "" Then
                    
                            Sheet2.Cells(BW_Row, BW_Clm) = "It's a Match!"
                    
                        Else
                                
                            Sheet2.Cells(BW_Row, BW_Clm) = "Sorry, it's not a match"
                            
                    
                    End If
                    
                End If
                    
        
        BW_Row = BW_Row + 1
        
        Next d
        
    Next c
                  
 MsgBox "Matching Finished"

End Sub

Upvotes: 1

Views: 7331

Answers (2)

user2137354
user2137354

Reputation:

Using VBA seems unnecessary for this. You could just throw in this array formula in E2 (hit Ctrl+Shift+Enter):

=CHOOSE(MAX(IF(($C$2:$C$7=$A2)*($D$2:$D$7=$B2),2,1)),"Sorry, it's not a match","It's a Match!")

The IF function assigns value 2 if both conditions are TRUE and value 1 if FALSE. MAX will find the highest value out of the array of values. CHOOSE will return the phrase based on the value. 1 = "no match", 2 = "match".

Upvotes: 1

ARich
ARich

Reputation: 3279

+1 for @user2140261's comment... the VBA is going to be slower than your formula. However, if you're set on using VBA, insert this instead of your For Each C loop:

i = 11
For Each c In Table1
    If c = Cells(i, 8) Then
        If Cells(i, 7) = Cells(i, 9) Then sheet2.Cells(i, BW_Clm) = "It's a Match!"
    End If
    sheet2.Cells(i, BW_Clm) = "Sorry, it's not a match"
    i = i + 1
Next c

Tested

This will check F11 against H11. If it's a match, it checks G11 against I11, if that returns a match, "It's a match!" is written to J11. If it isn't a match, "Sorry, it's not a match" is written to J11. It then begins the loop over for row 12.

Upvotes: 1

Related Questions