Formula to accurately assign pay rate to employees based on multiple criteria
My company has a few employees who hold multiple positions, each with a separate pay rate.
I am trying to create invoices with a formula that automatically fills in the correct pay rate, and in the timesheet data, the only indicator of which position they should be paid for is a unique “activity code”.
My confusion comes from the fact that our time sheet activity codes are not typically used to identify specific wages or positions. Employees with only one position choose from a variety of activity codes based on their actual activity.
I’ve added a photo of an example spreadsheet showing the general setup I need to work with.
I am currently using an Index-Match formula that references a Comp Rate Reference Table and uses last names as the lookup value. This doesn’t account for the activity code. My current rudimentary workaround is to use conditional formatting to highlight cells with the unique activity code, then manually change it.
I know there must be a better way, but I am a bit lost and not very skilled with Excel, so any advice would be greatly appreciated!
[link] [comments]
Want to read more?
Check out the full article on the original site