Applying numeric values in one cell based on text in another

C

Chi_TechWriter

Hello All,

New and young technical writer in a bit of a stew due to my
inexperience with MS Excel formulas. The following outlines what I am
attempting to do, likely very basic to you gurus out there so please
forgive my newness to this.

In my worksheet, Column A contains a repeating series of drop down
menus created using data validation. The choices int he drop downs are
the names of courses offered by my training department.

In Column B I would like the number of billable hours each course is
worth automatically entered based on which course they entered in
column A. I know there is a simple formula to achieve this but I simply
am hitting walls so far.
 
P

Pete_UK

You will need to build up a table somewhere which lists all the courses
on offer together with the billable hours. You may already have half of
this - the lists for your data validation drop-downs could have another
column next to them with the appropriate hours. Let's assume this table
occupies cells L1 to M30.

In cell B1 you can enter the formula:

=IF(A1="","",VLOOKUP(A1,L$1:M$30,1,0))

and this can be copied down. If you have not made a selection in column
A then nothing will appear in B, but when you make a selection the
appropriate number of hours will appear in B.

Hope this helps.

Pete
 
P

Pete_UK

Sorry, the formula should be:

=IF(A1="","",VLOOKUP(A1,L$1:M$30,2,0))

i.e. return the data from the second column of the table.

Pete
 
Top