Restrict to last occurrence using OFFSET

[paulm88]

I am trying to find how to return the workers pay the maximum number of weeks after their start date. As you can see in the example below, I have tried to OFFSET by the number of weeks (G2). However, this is returning the pay for worker Bob instead of the last instance of Paul (£25). I basically need to restrict the lookup to the worker no matter if the number of weeks exceeds the instances of column A.

 

[steve the fish]

Could you paste a sample i can paste into excel rather than a picture?

 

[paulm88]

[TABLE="width: 500"]
<tbody>[TR]
[TD]Worker
[/TD]
[TD]Week[/TD]
[TD]Pay[/TD]
[TD][/TD]
[TD]Worker[/TD]
[TD]Start Week[/TD]
[TD]Max Weeks[/TD]
[TD]Final Pay[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]18/03/17[/TD]
[TD]£5[/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD]01/04/17[/TD]
[TD]5[/TD]
[TD]{=OFFSET(INDEX(C:C,MATCH(E2&F2,A:A&B:B,0)),G2,0)}
[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]25/03/17[/TD]
[TD]£10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]01/04/17[/TD]
[TD]£15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]08/04/17[/TD]
[TD]£20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]15/04/17[/TD]
[TD]£25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]01/04/17[/TD]
[TD]£40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]08/04/17[/TD]
[TD]£50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]15/04/17[/TD]
[TD]£60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]22/04/17[/TD]
[TD]£70[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

If im understanding correctly try this:

=LOOKUP(2,1/((A2:A100=E5)*(B2:B100<=(F5+G5*7))),C2:C100)

 

[paulm88]

Your formula works perfectly in theory and returns the value I would expect. However, the example I provided was poor! I was using OFFSET as it is not always the case that the dates will be every week, I've changed the example below.


[TABLE="width: 500"]
<tbody>[TR]
[TD]Worker[/TD]
[TD]Week[/TD]
[TD]Pay[/TD]
[TD][/TD]
[TD]Worker[/TD]
[TD]Start Week[/TD]
[TD]Max payments[/TD]
[TD]Final Pay[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]01/03/17[/TD]
[TD]£5[/TD]
[TD][/TD]
[TD]Paul[/TD]
[TD]06/09/17[/TD]
[TD]5[/TD]
[TD]{=OFFSET(INDEX(C:C,MATCH(E2&F2,A:A&B:B,0)),G2,0)}[/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]07/06/17[/TD]
[TD]£10[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]06/09/17[/TD]
[TD]£15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]22/11/17[/TD]
[TD]£20[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Paul[/TD]
[TD]07/03/17[/TD]
[TD]£25[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]13/04/17[/TD]
[TD]£40[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]04/05/17[/TD]
[TD]£50[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Bob[/TD]
[TD]25/05/17[/TD]
[TD]£60[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="bgcolor: #FAFAFA"]Bob[/TD]
[TD="bgcolor: #FAFAFA"]15/06/17[/TD]
[TD="bgcolor: #FAFAFA"]£70[/TD]
[TD="bgcolor: #FAFAFA"][/TD]
[/TR]
</tbody>[/TABLE]

 

[steve the fish]

The formula i provided doesnt care if you dont have dates every week. All it is doing is providing the last *date* that has the name paul and is before the 6/9/2017 + 5 weeks.

Edit. * Pay not date!

 

[paulm88]

Yeah, I can see that. Again, due to my poor example and the way I had been working it out, it wouldn't be weeks at all. So it would need to look for 5 rows down (changed to Max Payments rather than max weeks in the example), but stop at the last instance of Paul

 

[steve the fish]

Could you try to explain in words what you want? The last instance of paul?

 

[paulm88]

Basically, 5 rows down from Paul 06/09/17...but stop at the last instance of Paul if this is less than 5 rows down. I've changed the examples below that hopefully makes it a bit clearer. on the left, the formula {=OFFSET(INDEX(C5:C21,MATCH(A2&B2,A5:A21&B5:B21,0)),C2,0)} works because there are enough instances of "Paul". However, if I change the Start Week as shown in the example on the right, it returns a value attributed to Bob.

 

[steve the fish]

This array formula appears to do the job. It must be entered CTRL-SHIFT-ENTER.

=IFERROR(INDEX(C5:C21,SMALL(IF((A5:A21=A2)*(B5:B21>=B2),(ROW(A5:A21)-ROW(A4))),C2)),LOOKUP(2,1/(A5:A21=A2),C5:C21))