Here is a version that uses the search function (non-case-sensitive version of find). The set-up is as follows.

In sheet 1, the codes to be looked up begin in column A of Sheet 1. The final result will be in column B. Columns C, D ,etc .have the unique codes arranged horizontally in row 1, i.e., "BK" in C1, "R" in D2, etc. The practical way to achieve this is to simply copy the code list in the lookup table and Paste Special Transpose them horizontally in cells C1, D1, etc.

Then first in cell B2 enter the following formula:

```
=IF(NOT(ISERROR(SEARCH(C$1,$A2))),VLOOKUP(C$1,Sheet2!$A$2:$B$4,2,0),"")
```

Copy this formula across the rows from column C to how many code columns you Ceated in row 2.

Finally, in cell C2, concatenate all the results for row 2, i.e., the formula

```
=D2&" "&E2&" "&F2
```

etc., for all the columns with codes in row 1. This step is tedious, but can be shorted with the following VBA function, which allows all the cells in a range to be concatenated:

```
Function Concat(useThis As Range, Optional delim As String) As String
' this function will concatenate a range of cells and return the result as a single string
' useful when you have a large range of cells that you need to concatenate
' source: http://chandoo.org/wp/2008/05/28/how-to-add-a-range-of-cells-in-excel-concat/
Dim retVal As String, dlm As String, cell As Range
retVal = ""
If delim = Null Then
dlm = ""
Else
dlm = delim
End If
For Each cell In useThis
If CStr(cell.Value) <> "" And CStr(cell.Value) <> " " Then
retVal = retVal & CStr(cell.Value) & dlm
End If
Next
If dlm <> "" Then
retVal = Left(retVal, Len(retVal) - Len(dlm))
End If
Concat = retVal
End Function
```

You would insert and copy this function into a module in Developer VBA. Usage is simple--concat(C1:D1," "), for example.

Note that this approach works for all 2-character codes, and all 1-character codes if they are not in the 2+ character codes, that is, if there are no code pairs such as "R" and "BR".