In the attached there are two solutions on Sheet1. A simpler one (column F) that gives the same results as you expect from the data you provided, but on a test sheet (Sheet1 (2)) gives different results (see cell F407).
This is the of formula:
=INDEX($N$2:$N$13,MATCH($B2,$M$2:$M$13))


The more complex formula (column E) uses a different lookup table:
=IF(LEFT($B2,1)="B","BRYAN",IF(LEFT($B2,1)="U","AL MEDA",INDEX($K$2:$K$11,MATCH(VALUE(RIGHT($B2,3)),$ J$2:$J$11))))
gives more consistent results (see cell E407).


On Sheet1 there are comments in cells J2 and M2 regarding setting up the lookup tables.


It should be enough to get you started.

Attached Files

msOfficeForums39050Table Example.xlsx (30.2 KB, 11 views)