Having a number value based off text value

**bean4000** · 03-01-2013, 06:45 PM

I'm currently working with excel 2007. I have been throwing formulas at this all day and can't seem to get it to work. I have tried a variety to include "if" "value" "vlookup" but no luck. In column A I have each row with a drop down with 5 acronyms. So A4 as a drop down with black, blue, red, green, purple. A5 has the same list and so on for all column A. I want to have column D reflect a number passed off the selected choice from column A. So if I select black in A4, D4 will present the number 1. If I select blue in A5,D5 will represent 2 and so on or if A12 is black again D12 will again represent 1. I have made a list outside of my chart with the colors and numbers to allow for referencing in use with formulas as well but still can't get it right. Any help with this is greatly appreciated. I can also email anyone willing to help a screen shot if I didn't clarify this well enough. Thanks for looking.