Excel formula to lookup,match and calculate in a cell 1

[jfussell]

I have 2 worksheets, worksheet A is a pivot table of how many hours each employee spent on projects, worksheet B is a 2-column worksheet with each employee's name and pay per hour. The spelling of each employee's name on both worksheets are exact. Is there a way for excel to...
1) perform a lookup, based on worksheet A employee's name
2) and match it with worksheet B
3) then calculate the hourly pay times the hours worked on the projects

Any input on this would be greatly appreciated. Thanks.

 

[SkipVought]

jfussell

What does your PivotTable look like? Is it Names down the ROWS and Projects across Columns?

Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[SkipVought]

I think that I would recommend adding a column to the PivotTable Source Data for ProjectCost using the formula...
[tt]
=INDEX(HouryRate,MATCH(A2,Name,0))*C2
[/tt]
where
HouryRate is a NAMED RANGE of each person's pay rate and
Name is a NAMED RANGE of each person's Name and
column A is the Source Data Name and
column C is the Source Data Project Hours



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[rcrecelius]

I feel funny offering advice with my limited knowledge but I use the function vlookup quite often. I think the names will need to be in the left hand column(sorted alphabetically ascending) of both worksheets. With vlookup you can pull the hourly wage data over to the other sheet then calculate. For that matter, you could add the *c2 to the end of the vlookup I think.

Skipvought, Id like to know more aboutt the formula youve shown, looks like it does the same thing Ive mentioned.

 

[SkipVought]

rcrecelius,

I use Named column ranges almost exclusively, rather than multi-column ranges that you'd need to use the VLOOKUP function.

In my formula, the MATCH function looks up the Name and returns the row offset for the match.

The INDEX function returns the HourlRate based on the row offset from the MATCH.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jfussell]

Thanks for your input Skip.

Here's the layout of my pivot table...

Customer | Employee 1 | Employee 2 | Employee 3
Project 1| 90:45 | 13:15 | 70:00
Project 2|
Grand Total

I don't have a project cost to add to the pivot table. What I did was just create the same table format as above, but instead of the hours per project, it would just give me the salary cost per project.

Also, I've defined the Names respectively, then tried the formula, and for some reason it's not calculating correctly. I've got the HourlyRate and the Names, and I'm assuming the MATCH(A2, Name, 0) is to match the column that has the Employee 1 name to the Name list. Then the *C2 is to times the hours such as 90:45 in my layout above. However, the calculation came out $90.90 instead of $2181.49. I'm not sure if it has something to do with the cell format category, which I have the hours per project listed as a custom category with type [h]:mm. I hope I've given enough information to give you a better idea of how I have this setup. I'd appreciate any additional input you may have.

J

 

[SkipVought]

The problem is with you calculation. The TIME value is REALLY in DAYS. Each day is 24 hours. So you must factor each time by 24.



Skip,

http://www.TheOfficeExperts.com

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884

 

[jfussell]

Aaahahaha, thank you soo much. That was the ticket. I'm glad this calculation is finally working. Couldn't have done it without your input. Thanks again.