I would suggest that you arrange your first sheet in a similar way as
the second, with the dates going down the page and five columns - date,
primary, backup, supervisor, and manager. If you want to change the
layout of an existing sheet to this, then you can transpose the data
quite easily.

In a third sheet you can have a simple table listing the hourly rate
for each of the four functions, i.e.:

Primary 60.00
Backup 80.00
Supervisor 100.00
Manager 120.00

Obviously, I'm guessing the rates, but assume this table occupies A1:B4
of Sheet3.

In your second sheet, with the first date being the same as the first
date in Sheet1, enter this formula in cell B2:

=IF(ISNA(MATCH(B$1,Sheet1!$B2:$E2,0)),"",INDEX(She et3!$A$1:$B$4,MATCH(B$1,Sheet1!$B2:$E2,0),2))

This can be copied across for as many names as you have. Then highlight
these cells and copy down for as many dates as you have.

This will give you the four rates as appropriate, and leave blanks
where a person did not work that day - you can change the "" in the
middle of the formula to 0 if you prefer.

Hope this helps.

Pete