Index Match to return a rate

sploits

New Member
Joined
Mar 26, 2019
Messages
4
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
 

Some videos you may like

Excel Facts

Can Excel fill bagel flavors?
You can teach Excel a new custom list. Type the list in cells, File, Options, Advanced, Edit Custom Lists, Import, OK

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
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

New Member
Joined
Mar 26, 2019
Messages
4
Hi DanteAmor,

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

Thank you so much for your help
 

DanteAmor

Well-known Member
Joined
Dec 3, 2018
Messages
12,593
Office Version
  1. 2007
Platform
  1. Windows
I'm glad to help you. Thanks for the feedback.
 

Watch MrExcel Video

Forum statistics

Threads
1,114,235
Messages
5,546,648
Members
410,752
Latest member
MC01_
Top