I am trying to index a range where the column to index is based on a match "REGHRS" and then two further matches. The workbook has two tabs, one named "Reports by Dept" and the other named "Register". The "Reports by Dept" tries to find the hours and amounts based on dept and shift. For example, I need to find the RegAmount for Cars, Second shift, as the Register changes order each week, I do not know what the index column will always be , so I need the index to find a match for RegAmount and then also find the correct row for Cars, second shift and give me a value of 694.27
[TABLE="border: 0, cellpadding: 0, cellspacing: 0, width: 851"]
[TD="align: left"] Department
[/TD]
[TD="align: left"] Shift
[/TD]
REGHrs
REGAmount
DTHrs
DTAmount
OTHrs
OTAmount
[TD="align: left"] Dolls
[/TD]
[TD="align: left"] First Shift
[/TD]
30.93
463.95
1.73
25.95
[TD="align: left"] Cars
[/TD]
[TD="align: left"] First Shift
[/TD]
89.25
1388.76
15.50
471.20
[TD="align: left"] Cars
[/TD]
[TD="align: left"] Second Shift
[/TD]
48.63
694.27
[TD="align: left"] Cars
[/TD]
[TD="align: left"] Third Shift
[/TD]
80.00
1178.32
1.10
22.87
[TD="align: left"] Trucks
[/TD]
[TD="align: left"] First Shift
[/TD]
61.50
982.22
8.08
286.84
8.00
213.04
[TD="align: left"] Trucks
[/TD]
[TD="align: left"] Second Shift
[/TD]
62.50
842.02
[TD="align: left"] Trucks
[/TD]
[TD="align: left"] Third Shift
[/TD]
58.50
789.70
[TD="align: left"] Balls
[/TD]
[TD="align: left"] First Shift
[/TD]
64.00
928.00
7.00
153.00
[TD="align: left"] Balls
[/TD]
[TD="align: left"] Second Shift
[/TD]
64.00
1045.76
13.70
312.27
[TD="align: left"] Balls
[/TD]
[TD="align: left"] Third Shift
[/TD]
24.00
363.12
10.30
227.94
[/TABLE]
This is the formula I current have, but it's not working.
{=INDEX((Register!J5:BC42),MATCH(Register!$C$11:$C$42='Reports by Dept.'!DB$1, 0),MATCH(Register!$D$11:$D$42='Reports by Dept.'!DA$1, 0),MATCH(Register!J5:BA5="REGHRS", 0))}