Code to display text & code in a combo list, but display only code when selected

Hi,

I think I'm close to getting this right, but my code still isn't working. Would really appreciate any expert advice to get the macros working with my Antibiotic drop down combo list in the attached Excel file.

Using AbxCombined Range as my drop down view (antibiotic names spelled out) and AbxAbbv Range as my display (abbreviation of antibotic names) after an option is selected. Where am I off in my formula? When I select an option from the drop down, it displays what you see, not the abbreviated name from the AbxAbbv Range.

My code is below and workbook is attached.

Thanks!


Private Sub Worksheet_Change(ByVal Target As Range)
On Error GoTo errHandler

Dim wsLookupTbl As Worksheet
Set wsLookupTbl = Worksheets("LookupTbl")

If Target.Cells.Count > 1 Then GoTo exitHandler

If Target.Column = 36 Then
If Target.Value = "" Then GoTo exitHandler

Application.EnableEvents = False

Target.Value = wsLookupTbl.Range("A1") _
.Offset(Application. _
WorksheetFunction _
.Match(Target.Value, _
wsLookupTbl.Range("AbxCombined"), 0), 0)

End If

exitHandler:
Application.EnableEvents = True
Exit Sub

errHandler:
If Err.Number = 13 Or Err.Number = 1004 Then
GoTo exitHandler
Else
Resume Next
End If

End Sub