## How to assign a numerical value to a word in Excel

105 pts.
Tags:
Microsoft Excel
Microsoft Excel 2003
Microsoft Excel macros
I want to be able to select a material from a drop down list in column A, then type in the time in Column B and in a third column have the cost appear. What I need is to associate eg. VHS as having a rate card of £30. I can make lists and I can write the simple equation in the cost column to multiply the time with the material. I just can't figure out how to get Excel to assign values to the items on my drop down list. It seems so simple so I'm guessing there's an easy macro or formula that I can use- I just can't find it yet! My table looks like this:

`A B C 1 Material Time(in hours) Cost 2 3 VHS 1.5 =B3* ? 4 DVD 1.25 5 CD 0.75 `

I also have a list of the words and there values on another page though this can move: It's these two columns that I need to link I presume?

`J K 1 material rate card 2 VHS 30 3 Netsend 25 4 DVD 20 5 FRC 60`

I spent 8 hours trying to do this yesterday- help is very much appreciated!!! (I'm using Excel 2003)

Thanks. We'll let you know when a new response is added.

The VLOOKUP function should meet your needs. Here’s a good online Excel resource. Another similar question on a forum can be found here also.

In the IT trenches? So am I – read my IT Trenches blog.

## Discuss This Question: 5 Replies

Thanks. We'll let you know when a new response is added.

By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
• Hi thanks, I have been looking at the VLOOKUP I just can't quite make it work! I guess what I realy need is a template where someone has done the same thing and then just alter it. But searching the web, finding free templates is hard!
• Hmmm … Read Vlookup chapter again. It is not that hard. Here is an example: Suppose Sheet2 (Cost_Table) has the cost info in cells A2:B6 (CD,DVD,FRC,Netsend,VHS) – put them in ascending order. ```On Sheet1 Cell A2 (enter Material say FRC), on B2 enter =Vlookup(A2,Cost_Table!A2:B6,2) On C2 enter the hour & on D2 enter = B2 * C2``` This should do – if not post again with detailed info. Good luck.