Excel Data Validation drop-down list

Hi I’m new to this so I apologize in advance if I don’t ask my question in the correct way.
I’m using Excel 97 and I’ve created a data validation drop down list to be use in column A the actual list resides in column AZ.

The drop down box in column A works fine but I need to automatically assign a numeric value in a separate column for the item selected in column A.

For example if I select BLUE in cell A1 the number 10 will automatically be placed in D1. Select RED in A10 the number 20 will be placed in D10. Essentially I want to assign an ID number for each item in column AZ. I’VE looked in FAQ but I haven’t found the answer. Can someone help?

Hope this makes sense.

Thank You