Index Match to return a rate

[sploits]

Hi,

I have been racking my brains for the past 3 hours trying to make a formula work for me.

I have a table in which contains a Date From, a Date To, a persons name and then 2 columns for charge out rate (basic time and overtime).

In my master sheet I want to be able to book a person at a given time and the formula would look at the date the person was booked, their name, the shift they are doing and give the corresponding rate.

I have tried a few index match formulas but I can't seem to get the final result I need

Google Drive Link for an example:
https://drive.google.com/open?id=1O9LQubAUVxMo4Ac39PBpQiNEVFcqm7xV

In the yellow box on the example file I would like the correct rate to appear for the shift he person is working. I just can't figure it out with an index match (array and non array). Any help would be fantastic.

Many thanks

 

[DanteAmor]

Try this

<b>Sheet1</b><br /><br /><table border="1" cellspacing="0" style="font-family:Calibri,Arial; font-size:11pt; background-color:#ffffff; "> <colgroup><col style="font-weight:bold; width:30px; " /><col style="width:73px;" /><col style="width:84px;" /><col style="width:102px;" /><col style="width:64px;" /><col style="width:74px;" /><col style="width:64px;" /></colgroup><tr style="background-color:#cacaca; text-align:center; font-weight:bold; font-size:8pt; "><td > </td><td >A</td><td >B</td><td >C</td><td >D</td><td >E</td><td >F</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >1</td><td style="font-weight:bold; ">Input</td><td > </td><td > </td><td > </td><td > </td><td > </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >2</td><td >Date</td><td >Name</td><td >Shift</td><td >Hours</td><td >Rate</td><td >Total</td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >3</td><td style="text-align:right; ">02/05/2018</td><td >Adam Apple</td><td >Basic</td><td style="text-align:right; ">8</td><td style="background-color:#ffff00; color:#ff0000; text-align:right; "> £          8.00 </td><td style="text-align:right; "> £    64.00 </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >4</td><td style="text-align:right; ">25/03/2019</td><td >Claire Cherry</td><td >Overtime</td><td style="text-align:right; ">3</td><td style="background-color:#ffff00; color:#ff0000; text-align:right; "> £        21.00 </td><td style="text-align:right; "> £    63.00 </td></tr><tr style="height:18px ;" ><td style="font-size:8pt; background-color:#cacaca; text-align:center; " >5</td><td style="text-align:right; ">25/03/2019</td><td >Adam Apple</td><td >Overtime</td><td style="text-align:right; ">3</td><td style="background-color:#ffff00; color:#ff0000; text-align:right; "> £        19.50 </td><td style="text-align:right; "> £    58.50 </td></tr></table><br /><table style="font-family:Arial; font-size:10pt; border-style: groove ;border-color:#00ff00;background-color:#fffcf9; color:#000000; "><tr><td ><b>Formula</b></td></tr><tr><td ><table border = "1" cellspacing="0" cellpadding="2" style="font-family:Arial; font-size:9pt;"><tr style="background-color:#cacaca; font-size:10pt;"><td >Cell</td><td >Formula</td></tr><tr><td >E3</td><td >=SUMPRODUCT((Table1[Name]=Table2[[#This Row],[Name]])*(Table1[Date To]<=Table2[[#This Row],[Date]])*(Table1[Date From]>=Table2[[#This Row],[Date]])*(INDIRECT("Table1[" & Table2[[#This Row],[Shift]] & "]")))</td></tr></table></td></tr></table> <br /><br />

 

[DanteAmor]

This is another way:

=SUMIFS(INDIRECT("Table1[" &Table2[[#This Row],[Shift]] & "]"),Table1[Date To],"<=" & Table2[[#This Row],[Date]],Table1[Date From],">="&Table2[[#This Row],[Date]],Table1[Name],Table2[[#This Row],[Name]])

 

[sploits]

Hi DanteAmor,

That appears to have worked perfectly! Now to link it into my master file.

Thank you so much for your help

 

[DanteAmor]

I'm glad to help you. Thanks for the feedback.