Macro not finding partial matches

User enters either a First Name in D4, Family Name in D6 or Project in Col D9 of the Input Form

For this example Database has variations on two Project names in Col C, but the "real" file can also have variations in First or Family Names.

Need Macro to search each Database row. If it finds a record with a partial or whole match to whatever is in Col D, it should copy the Data to the next free row from L4 downwards.

Unfortunately it isn't doing it, but I can't see why.

Option Explicit
Dim e As Long, f As Long, x As Long

Sub FINDMATCHES()

    With Sheet2
    f = .Cells(.Rows.Count, "A").End(xlUp).Row
    End With
    
    e = 4
    
    With Sheet1
    
    For x = 3 To f
   
        If InStr(1, .Range("D4").Value, Sheet2.Range("A" & x).Value, vbTextCompare) > 0 Then
        .Range("L" & e) = Sheet2.Range("D" & x)
        .Range("M" & e) = Sheet2.Range("A" & x)
        .Range("N" & e) = Sheet2.Range("B" & x)
        .Range("O" & e) = Sheet2.Range("C" & x)
        e = e + 1
        
        ElseIf InStr(1, Sheet1.Range("D6").Value, Sheet2.Range("B" & x).Value, vbTextCompare) > 0 Then
                
        .Range("L" & e) = Sheet2.Range("D" & x)
        .Range("M" & e) = Sheet2.Range("A" & x)
        .Range("N" & e) = Sheet2.Range("B" & x)
        .Range("O" & e) = Sheet2.Range("C" & x)
        e = e + 1
                        
        ElseIf InStr(1, Sheet1.Range("D9").Value, Sheet2.Range("C" & x).Value, vbTextCompare) > 0 Then
        .Range("L" & e) = Sheet2.Range("D" & x)
        .Range("M" & e) = Sheet2.Range("A" & x)
        .Range("N" & e) = Sheet2.Range("B" & x)
        .Range("O" & e) = Sheet2.Range("C" & x)
        e = e + 1
        
        ElseIf InStr(1, Sheet2.Range("D9").Value, Sheet2.Range("D" & x).Value, vbTextCompare) > 0 Then
        .Range("L" & e) = Sheet2.Range("D" & x)
        .Range("M" & e) = Sheet2.Range("A" & x)
        .Range("N" & e) = Sheet2.Range("B" & x)
        .Range("O" & e) = Sheet2.Range("C" & x)
        e = e + 1
        
        End If
        
        Next
       
    End With
       
End Sub

Any suggestions, pointers, solutions or alternatives welcomed as ever.

Ochimus