can you look up info from a validated drop down menu in excel 2007

[Jason Jessup]

I have
a b
2 Name Wage
3 $-
4 existing employee $20.00
5 existing employee $25.00
6 existing employee $17.00
7 Labor Ready ID $10.00
8 Labor Ready UT $10.00
9 Labor Ready WA $10.00
10 Future Employee $-
11 Future Employee $-
12 Future Employee $-

in another work sheet i have

a&b c d i
5 Labor cost
6 Name hours rate Total Cost
7
8
9

in a7-9 i have a drop menu based on a name I created employee.
employee='Employee records'!$A$3:$A$54
I have also created two other name that are rate and name.
name='Employee records'!$A$3,'Employee records'!$A$4,'Employee
records'!$A$5,'Employee records'!$A$6,etc...to54
rate='Employee records'!$C$3,'Employee records'!$C$4,'Employee
records'!$C$5,'Employee records'!$C$6,etc...to54

what I would like is an equation (i was hoping) like in
D7=lookup(A7,name,rate)

The end goal is to select an employee from the dropdown menu and when that
happens the rate is update corralating to that employee. I want it to work
this way so that it is really easy to copy and past throughout the workbook,
as I have multiple form that have a labor cost area and already have update
all corresponding cells for name to be drop down menus.

Any help would be great, thank you in advance.

 

[AdamV]

You can use VLOOKUP for this, if I understand your problem correctly.

try: =VLOOKUP(A7,Sheet1!$A$2:$B$12,2,FALSE)

where A7 is the name chosen in the dropdown
Sheet1 is where the table of rates is, in cells A2:B12 (possibly you
have more rows than this, just use a big enough number in place of 12).
2 tells it to return the value from the second column of the matching row

Hope this helps
Adam
MCAS:Instructor