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]

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

<tbody>
</tbody>

 

[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.

Worker	Week	Pay		Worker	Start Week	Max payments	Final Pay
Paul	01/03/17	£5		Paul	06/09/17	5	{=OFFSET(INDEX(C:C,MATCH(E2&F2,A:A&B:B,0)),G2,0)}
Paul	07/06/17	£10
Paul	06/09/17	£15
Paul	22/11/17	£20
Paul	07/03/17	£25
Bob	13/04/17	£40
Bob	04/05/17	£50
Bob	25/05/17	£60
Bob	15/06/17	£70

<tbody>
</tbody>

 

[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))