Looking up and applying effective dates based on a working date per person

[dv8123456789]

Hi - I have a tab within excel that is an assignment list which details people and effective dates\rates. There are multiple people and one person may have more than one line if their rate changes mid year.

I then have a tab containing timesheet data for various people and I want to be able to look up for a particular person based on the date worked what the rate should be so I can calculate the cost.

I have seen vlookups with a true condition that fit within a range, but I cannot see how this will work given that I need it to effectively filter by person before doing the range lookup. I'm thinking perhaps sumifs of the rate if the person in the timesheet data =person in the rates table and the effective date is less than the date worked, but that could still apply to multiple rate lines so I need to narrow it down further ideally by finding the right person and then max of the effective date that is still earlier than the time date worked. Any ideas? Any help would be greatly appreciated

 

[wideboydixon]

Assuming dates are ascending on the rate table then something like this:

Book1
	A	B	C	D	E	F
1	Person	Date	Rate		Person	Diane
2	Alice	01/01/2017	10		Date	12/05/2017
3	Bob	01/01/2017	11		Rate	20
4	Charlie	01/01/2017	12
5	Diane	01/01/2017	13
6	Alice	01/02/2017	14
7	Bob	01/02/2017	15
8	Charlie	01/02/2017	16
9	Charlie	01/03/2017	17
10	Bob	01/04/2017	18
11	Alice	01/05/2017	19
12	Diane	01/05/2017	20
Sheet1

Cell Formulas
Range	Formula
F3	{=INDEX($C$2:$C$12,MAX(IF($A$2:$A$12=$F$1,IF($B$2:$B$12<=$F$2,ROW($B$2:$B$12)-ROW($B$2)+1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[dv8123456789]

thanks for your reply WBD! It seems to work for a single cell but I'm struggling to auto populate multiple cells (sorry, not that familiar with arrays). Using your example, I've populated some dummy data in columns H,I and J and amended formula to {=INDEX($C$2:$C$12,MAX(IF($A$2:$A$12=$H1,IF($B$2:$B$12<=$I2,ROW($B$2:$B$12)-ROW($B$2)+1))))} so that its not a fixed reference but will change depending on the row

J2 calculates correctly the rate of 10, but I'd expect a rate change in J3 and the subsequent cells, but I'm not getting that. Do you know why please? Slightly separate question. If I wanted to return a text string instead of a rate that was applicable at a point in time using the same logic would this work do you think? Thanks again

[TABLE="width: 836"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD="align: right"]1[/TD]
[TD]Person[/TD]
[TD]Date[/TD]
[TD]Rate[/TD]
[TD] [/TD]
[TD]Person[/TD]
[TD]Alice[/TD]
[TD] [/TD]
[TD]Person[/TD]
[TD]Date[/TD]
[TD]Rate[/TD]
[/TR]
[TR]
[TD="align: right"]2[/TD]
[TD]Alice[/TD]
[TD]01/01/2017[/TD]
[TD]10[/TD]
[TD] [/TD]
[TD]Date[/TD]
[TD]03/02/2017[/TD]
[TD][/TD]
[TD]Alice[/TD]
[TD]31/01/2017[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]3[/TD]
[TD]Bob[/TD]
[TD]01/01/2017[/TD]
[TD]11[/TD]
[TD] [/TD]
[TD]Rate[/TD]
[TD]11[/TD]
[TD][/TD]
[TD]Alice[/TD]
[TD]01/02/2017[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]4[/TD]
[TD]Charlie[/TD]
[TD]01/01/2017[/TD]
[TD]12[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]Alice[/TD]
[TD]02/02/2017[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]5[/TD]
[TD]Diane[/TD]
[TD]01/01/2017[/TD]
[TD]13[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD]alice[/TD]
[TD]03/02/2017[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD="align: right"]6[/TD]
[TD]Alice[/TD]
[TD]01/02/2017[/TD]
[TD]14[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]7[/TD]
[TD]Bob[/TD]
[TD]01/02/2017[/TD]
[TD]15[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]8[/TD]
[TD]Charlie[/TD]
[TD]01/02/2017[/TD]
[TD]16[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]9[/TD]
[TD]Charlie[/TD]
[TD]01/03/2017[/TD]
[TD]17[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]10[/TD]
[TD]Bob[/TD]
[TD]01/04/2017[/TD]
[TD]18[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]11[/TD]
[TD]Alice[/TD]
[TD]01/05/2017[/TD]
[TD]19[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]12[/TD]
[TD]Diane[/TD]
[TD]01/05/2017[/TD]
[TD]20[/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody><colgroup><col span="2"><col span="2"><col span="2"><col><col span="2"><col><col></colgroup>[/TABLE]

 

[wideboydixon]

Book1
	A	B	C	D	E	F	G	H	I	J
1	Person	Date	Rate					Person	Date	Rate
2	Alice	01/01/2017	10					Alice	31/01/2017	10
3	Bob	01/01/2017	11					Alice	01/02/2017	14
4	Charlie	01/01/2017	12					Alice	02/02/2017	14
5	Diane	01/01/2017	13					Alice	03/02/2017	14
6	Alice	01/02/2017	14
7	Bob	01/02/2017	15
8	Charlie	01/02/2017	16
9	Charlie	01/03/2017	17
10	Bob	01/04/2017	18
11	Alice	01/05/2017	19
12	Diane	01/05/2017	20
Sheet1

Cell Formulas
Range	Formula
J2	{=INDEX($C$2:$C$12,MAX(IF($A$2:$A$12=$H2,IF($B$2:$B$12<=$I2,ROW($B$2:$B$12)-ROW($B$2)+1))))}
Press CTRL+SHIFT+ENTER to enter array formulas.

WBD

 

[dv8123456789]

Thanks for your reply. Hopefully almost there. It might (definitely) just be a lack of understanding on my part, but I can't seem to get it to work. From doing a bit of research on array formulas I'm highlighting J2:J5, putting in the formula (minus the {}) and then hitting CTRL+SHIFT+ENTER, which gives me the {}. I get the same result (10) across all cells in J2 through J5? Do you know why this might be?

 

[wideboydixon]

Don't highlight all cells. Enter the formula in J2 then copy J2 to J3:J5.

WBD

 

[dv8123456789]

Thank you so much!!!!!! works like a charm