Have a spreadsheet at work for entering materials used. Col A is where the material is entered (can't use a drop down list, as quite often uncommon materials are entered) Col B and C are 'Material Code' and 'Unit of Measurement' respectively.

My question, is there a way to have a list off to the side (ie Col AA, AB, AC) with all the common materials/code/units, and a formula that would says "If item in Col A appears in Col AA, match item in col AB and place in col B) Example, if I enter 'Stop Sign' in Col A, it searches Col AA. When it finds 'Stop Sign', it looks in Col AB and sees the code is 123. It then places 123 into Col B beside 'Stop Sign'

I can't place a formula in Col B, as there are times that I still need to be able to manually enter information in that cell.

I'm using Excel 2003 (my company is very progressive) and would appreciate a solution that doesn't require a macro if possible. Thanks for your time!