While this thread continues...
Fully agree with using Named Ranges.
From Excel 2007 the maximum nested IF statements increases from 7 to 64.
See here for other limit increases:
Microsoft Excel Team Blog
From Excel 2007, you can use the IFERROR() function.
=IFERROR(VLOOKUP(B12, $A$5:$B$8, 2, FALSE), "")
Also, if the colon must appear after the trade name, you could format them that way with a custom format.
That way, even though the cell contains the word "Builder" you can make it say "Builder:"
Right-click the cell, Format Cells...
From the Number tab, select Custom
Set Type to @: