Try...

=VLOOKUP(A21,'Codes
All'!$A$4:$Q$170,MATCH(B21,{"A","B","C","D","T","R "},0)+3,0)

Hope this helps!

In article . com,
"Eintsein_mc2" wrote:

I was wondering if there is a way to simplify this formula? Thanks
=IF(B21="A",VLOOKUP(A21,'Codes
All'!$A$4:$Q$170,4,FALSE),IF(B21="B",VLOOKUP(A21,' Codes
All'!$A$4:$Q$170,5,FALSE),IF(B21="C",VLOOKUP(A21,' Codes
All'!$A$4:$Q$170,6,FALSE),IF(B21="D",VLOOKUP(A21,' Codes
All'!$A$4:$Q$170,7,FALSE),IF(B21="T",VLOOKUP(A21,' Codes
All'!$A$4:$Q$170,8,FALSE),IF(B21="R",VLOOKUP(A21,' Codes
All'!$A$4:$Q$170,9,FALSE),""))))))