## 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)

## Answer Wiki

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

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
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!
105 pointsBadges:
report
• 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.
2,540 pointsBadges:
report
• The key to the VLOOKUP command is that the values in the lookup column need to be in ascending order. If they are not in ascending order, the results will be incorrect. In the IT trenches? So am I - read my IT-Trenches blog
0 pointsBadges:
report
• Note that the Excel application help also gives an example at the bottom. You can cut/paste it and test it out. In the IT trenches? So am I - read my IT-Trenches blog
0 pointsBadges:
report
• Hi I've actually solved that problem and now have bigger ones! thanks for all the comments though. Out of interest: I found out that someone at work had made something similar and once I cracked the password I could see all his formulas and unpick it. His was ridiculously complicated but basically I used a combination of the INDEX and VLOOKUP functions several times and a strange list box that isn't attached to a cell and that I can't fgure how he got in the first place. but it works. Of course I didn't think it through very well, the only way to do it for every line is to repeat all the behind the scenes lists everytime. Not practical when I could in theory have an infinite number of lines. So I've now progressed and have spent the last 8 hours creating a Userfrom in VisualBasic. It's going ok- I've set it up, I've now just got to do exactly the same thing as before- when you select from the combobox and enter a time (i.e the quantity) I want the cost to appear in another box. So I''m stuck again! having never even seen VB before I'm finding it quite hard, and I still can't write the lines that make the form open automatically when you first open the excel file. I thought it was: Private Sub Workbook_Open () Worksheets (Sheet1"). Activate frmMcr.Show End Sub and also how to set the save button up so that it saves the data you enter into the spreadsheet. So really, I have lots left today and only another 2 days to do it in! ideas even more welcome.... Thanks
105 pointsBadges:
report