Stuck with SUM using Index Match or Vlookup

So, I have a schedule file and we code the schedules of each of the employees in there and the different codes have different hours. I will be coding the schedules staring E4 to AH4 horizontally for the month for one agent. I have another sheet named as Shifts & Legends, I have the legends in Tab Shifts & Legends Starting from B2 to B55, and their Hours from D2 to D55. I need to pick shifts coded from E4 to AH4 in Duty Rota tab and look for the hours in legends and rota tab for shifts coded and add those hours in the Duty Rota Tab in cell AO4. I had used Index Match earlier as

=SUM(INDEX('Shifts & Legends'!D:D, MATCH(E4:AH4, 'Shifts & Legends'!B:B, 0))) and

A Vlookup as

=SUM(VLOOKUP(E4:AH4, 'Shifts & Legends'!$B$2:$D$55, 3, FALSE))

but none of them seem to work the way they should. Any help with this is appreciated.

Try this,

Please Login or Register  to view this content.

You are welcome.