Using Excel, Based On One Column Input, Receive Specific Response Other Column ... Improvements?

Question

Can this code be improved ? I'm using a SELECT CASE statement to filter input terms resulting in a matched term. Thank you.

Option Explicit

Private Sub Worksheet_SelectionChange(ByVal Target As Range)
Dim r As Long
For r = 2 To Cells(Rows.Count, "I").End(xlUp).Row
    Select Case Cells(r, "I").Value
        Case "Marketing"
            Cells(r, 11).Value = "John Smith"
        Case "Desktop Support"
            Cells(r, 11).Value = "Jane Doe"
        Case Else
            Cells(r, 11).Value = "Not Defined"
    End Select
Next

End Sub

Answer 1

Create a lookup table with all the input and output terms:

Use the following formula to lookup the values:

=IFERROR(INDEX(J$2:J$3,MATCH(A2,I$2:I$3,0),1),"Not Defined")