Private Sub Worksheet_Change(ByVal Target As Range)
        For Each cell In Target
         If Not Intersect(cell, Range("D2:D1000")) Is Nothing And _
                                       Target.Offset(0, -2) = "" Then
                With Target.Offset(0, -2)
                    .Value = Date
                    .EntireColumn.AutoFit
                End With
       End If
    Next cell
End Sub

=IFERROR(LOOKUP(2,1/SEARCH($C$2:$C$4,E6),$B$2:$B$4),"не найден")