find the last value within the range that meet the criteria. I have a fomular but it does not return the corrent answer

[agasi]

I have this formula which worked so well so far. But when I looked for May wk1, it returned the last value which is 21 July 2017 instead of 06 May 2017. I cannot figure out it does not work. If I shorten the lookup and result range, then it works. =LOOKUP("MAY wk1",{"APR wk5";"APR wk6";"MAY wk1";"MAY wk1";"MAY wk1";"MAY wk1";"MAY wk1";"MAY wk1";"MAY wk2";"MAY wk2";"MAY wk2";"MAY wk2";"MAY wk2";"MAY wk2";"MAY wk2";"MAY wk3";"MAY wk3";"MAY wk3";"MAY wk3";"MAY wk3";"MAY wk3";"MAY wk3";"MAY wk4";"MAY wk4";"MAY wk4";"MAY wk4";"MAY wk4";"MAY wk4";"MAY wk4";"MAY wk5";"MAY wk5";"MAY wk5";"MAY wk5";"JUN wk1";"JUN wk1";"JUN wk1";"JUN wk2";"JUN wk2";"JUN wk2";"JUN wk2";"JUN wk2";"JUN wk2";"JUN wk2";"JUN wk3";"JUN wk3";"JUN wk3";"JUN wk3";"JUN wk3";"JUN wk3";"JUN wk3";"JUN wk4";"JUN wk4";"JUN wk4";"JUN wk4";"JUN wk4";"JUN wk4";"JUN wk4";"JUN wk5";"JUN wk5";"JUN wk5";"JUN wk5";"JUN wk5";"JUN wk5";"JUL wk1";"JUL wk2";"JUL wk2";"JUL wk2";"JUL wk2";"JUL wk2";"JUL wk2";"JUL wk2";"JUL wk3";"JUL wk3";"JUL wk3";"JUL wk3";"JUL wk3";"JUL wk3";"JUL wk3";"JUL wk4";"JUL wk4";"JUL wk4";"JUL wk4";"JUL wk4";"JUL wk4"},{"29 Apr 2017";"30 Apr 2017";"01 May 2017";"02 May 2017";"03 May 2017";"04 May 2017";"05 May 2017";"06 May 2017";"07 May 2017";"08 May 2017";"09 May 2017";"10 May 2017";"11 May 2017";"12 May 2017";"13 May 2017";"14 May 2017";"15 May 2017";"16 May 2017";"17 May 2017";"18 May 2017";"19 May 2017";"20 May 2017";"21 May 2017";"22 May 2017";"23 May 2017";"24 May 2017";"25 May 2017";"26 May 2017";"27 May 2017";"28 May 2017";"29 May 2017";"30 May 2017";"31 May 2017";"01 Jun 2017";"02 Jun 2017";"03 Jun 2017";"04 Jun 2017";"05 Jun 2017";"06 Jun 2017";"07 Jun 2017";"08 Jun 2017";"09 Jun 2017";"10 Jun 2017";"11 Jun 2017";"12 Jun 2017";"13 Jun 2017";"14 Jun 2017";"15 Jun 2017";"16 Jun 2017";"17 Jun 2017";"18 Jun 2017";"19 Jun 2017";"20 Jun 2017";"21 Jun 2017";"22 Jun 2017";"23 Jun 2017";"24 Jun 2017";"25 Jun 2017";"26 Jun 2017";"27 Jun 2017";"28 Jun 2017";"29 Jun 2017";"30 Jun 2017";"01 Jul 2017";"02 Jul 2017";"03 Jul 2017";"04 Jul 2017";"05 Jul 2017";"06 Jul 2017";"07 Jul 2017";"08 Jul 2017";"09 Jul 2017";"10 Jul 2017";"11 Jul 2017";"12 Jul 2017";"13 Jul 2017";"14 Jul 2017";"15 Jul 2017";"16 Jul 2017";"17 Jul 2017";"18 Jul 2017";"19 Jul 2017";"20 Jul 2017";"21 Jul 2017"}) This is the look up table. 29/04/2017 APR wk5 30/04/2017 APR wk6 01/05/2017 MAY wk1 02/05/2017 MAY wk1 03/05/2017 MAY wk1 04/05/2017 MAY wk1 05/05/2017 MAY wk1 06/05/2017 MAY wk1 07/05/2017 MAY wk2 08/05/2017 MAY wk2 09/05/2017 MAY wk2 10/05/2017 MAY wk2 11/05/2017 MAY wk2 12/05/2017 MAY wk2 13/05/2017 MAY wk2 14/05/2017 MAY wk3 15/05/2017 MAY wk3 16/05/2017 MAY wk3 17/05/2017 MAY wk3 18/05/2017 MAY wk3 19/05/2017 MAY wk3 20/05/2017 MAY wk3 21/05/2017 MAY wk4 22/05/2017 MAY wk4 23/05/2017 MAY wk4 24/05/2017 MAY wk4 25/05/2017 MAY wk4 26/05/2017 MAY wk4 27/05/2017 MAY wk4 28/05/2017 MAY wk5 29/05/2017 MAY wk5 30/05/2017 MAY wk5 31/05/2017 MAY wk5 01/06/2017 JUN wk1 02/06/2017 JUN wk1 03/06/2017 JUN wk1 04/06/2017 JUN wk2 05/06/2017 JUN wk2 06/06/2017 JUN wk2 07/06/2017 JUN wk2 08/06/2017 JUN wk2 09/06/2017 JUN wk2 10/06/2017 JUN wk2 11/06/2017 JUN wk3 12/06/2017 JUN wk3 13/06/2017 JUN wk3 14/06/2017 JUN wk3 15/06/2017 JUN wk3 16/06/2017 JUN wk3 17/06/2017 JUN wk3 18/06/2017 JUN wk4 19/06/2017 JUN wk4 20/06/2017 JUN wk4 21/06/2017 JUN wk4 22/06/2017 JUN wk4 23/06/2017 JUN wk4 24/06/2017 JUN wk4 25/06/2017 JUN wk5 26/06/2017 JUN wk5 27/06/2017 JUN wk5 28/06/2017 JUN wk5 29/06/2017 JUN wk5 30/06/2017 JUN wk5 01/07/2017 JUL wk1 02/07/2017 JUL wk2 03/07/2017 JUL wk2 04/07/2017 JUL wk2 05/07/2017 JUL wk2 06/07/2017 JUL wk2 07/07/2017 JUL wk2 08/07/2017 JUL wk2 09/07/2017 JUL wk3 10/07/2017 JUL wk3 11/07/2017 JUL wk3 12/07/2017 JUL wk3 13/07/2017 JUL wk3 14/07/2017 JUL wk3 15/07/2017 JUL wk3 16/07/2017 JUL wk4 17/07/2017 JUL wk4 18/07/2017 JUL wk4 19/07/2017 JUL wk4 20/07/2017 JUL wk4 21/07/2017 JUL wk4 22/07/2017 JUL wk4 23/07/2017 JUL wk5 24/07/2017 JUL wk5 25/07/2017 JUL wk5 26/07/2017 JUL wk5 27/07/2017 JUL wk5 28/07/2017 JUL wk5 29/07/2017 JUL wk5 30/07/2017 JUL wk6 31/07/2017 JUL wk6 01/08/2017 AUG wk1 02/08/2017 AUG wk1 03/08/2017 AUG wk1 04/08/2017 AUG wk1 05/08/2017 AUG wk1 06/08/2017 AUG wk2 07/08/2017 AUG wk2 08/08/2017 AUG wk2 09/08/2017 AUG wk2 10/08/2017 AUG wk2 11/08/2017 AUG wk2 12/08/2017 AUG wk2 13/08/2017 AUG wk3 14/08/2017 AUG wk3 15/08/2017 AUG wk3 16/08/2017 AUG wk3 17/08/2017 AUG wk3 18/08/2017 AUG wk3 19/08/2017 AUG wk3 20/08/2017 AUG wk4 21/08/2017 AUG wk4 22/08/2017 AUG wk4 23/08/2017 AUG wk4 24/08/2017 AUG wk4 25/08/2017 AUG wk4 26/08/2017 AUG wk4 27/08/2017 AUG wk5 28/08/2017 AUG wk5 29/08/2017 AUG wk5 30/08/2017 AUG wk5 31/08/2017 AUG wk5 01/09/2017 SEP wk1 02/09/2017 SEP wk1 03/09/2017 SEP wk2 04/09/2017 SEP wk2 05/09/2017 SEP wk2 06/09/2017 SEP wk2 07/09/2017 SEP wk2 08/09/2017 SEP wk2 09/09/2017 SEP wk2 10/09/2017 SEP wk3 11/09/2017 SEP wk3 12/09/2017 SEP wk3 13/09/2017 SEP wk3 14/09/2017 SEP wk3 15/09/2017 SEP wk3 16/09/2017 SEP wk3 17/09/2017 SEP wk4 18/09/2017 SEP wk4 19/09/2017 SEP wk4 20/09/2017 SEP wk4 21/09/2017 SEP wk4 22/09/2017 SEP wk4 23/09/2017 SEP wk4 24/09/2017 SEP wk5 25/09/2017 SEP wk5 26/09/2017 SEP wk5 27/09/2017 SEP wk5 28/09/2017 SEP wk5 29/09/2017 SEP wk5 30/09/2017 SEP wk5 01/10/2017 OCT wk1 02/10/2017 OCT wk1 03/10/2017 OCT wk1 04/10/2017 OCT wk1 05/10/2017 OCT wk1 06/10/2017 OCT wk1 07/10/2017 OCT wk1 08/10/2017 OCT wk2 09/10/2017 OCT wk2 10/10/2017 OCT wk2 11/10/2017 OCT wk2 12/10/2017 OCT wk2 13/10/2017 OCT wk2 14/10/2017 OCT wk2 15/10/2017 OCT wk3 16/10/2017 OCT wk3 17/10/2017 OCT wk3 18/10/2017 OCT wk3 19/10/2017 OCT wk3 20/10/2017 OCT wk3 21/10/2017 OCT wk3 22/10/2017 OCT wk4 23/10/2017 OCT wk4 24/10/2017 OCT wk4 25/10/2017 OCT wk4 26/10/2017 OCT wk4 27/10/2017 OCT wk4 28/10/2017 OCT wk4 29/10/2017 OCT wk5 30/10/2017 OCT wk5 31/10/2017 OCT wk5 01/11/2017 NOV wk1 02/11/2017 NOV wk1 03/11/2017 NOV wk1 04/11/2017 NOV wk1 05/11/2017 NOV wk2 06/11/2017 NOV wk2 07/11/2017 NOV wk2 08/11/2017 NOV wk2 09/11/2017 NOV wk2 10/11/2017 NOV wk2 11/11/2017 NOV wk2 12/11/2017 NOV wk3 13/11/2017 NOV wk3 14/11/2017 NOV wk3 15/11/2017 NOV wk3 16/11/2017 NOV wk3 17/11/2017 NOV wk3 18/11/2017 NOV wk3 19/11/2017 NOV wk4 20/11/2017 NOV wk4 21/11/2017 NOV wk4 22/11/2017 NOV wk4 23/11/2017 NOV wk4 24/11/2017 NOV wk4 25/11/2017 NOV wk4 26/11/2017 NOV wk5 27/11/2017 NOV wk5 <tbody> </tbody><colgroup><col><col></colgroup>

<tbody>
</tbody><colgroup><col></colgroup>

 

[Aladin Akyurek]

LOOKUP here would not be a good idea...

Better:

1.

=INDEX(A:A,MATCH("MAY wk1",B:B,0))

2.

=VLOOKUP("MAY wk1<strike></strike>",CHOOSE({1,2},B:B,A:A),2,0)

 

[agasi]

I pressed the wrong button to post my reply to Aladin. Aladin, thanks. Can you please explain why the lookup does not work in this case? I have found a solution to return the last value in another thread using sumproduct. I think that it was you to reply to the question.

INDEX(TEXT($Q$2:$Q$366,"dd mmm yyyy"),SUMPRODUCT(MAX((INDEX(T2:T9,S1)=$R$2:$R$366)*ROW($R$2:$R$366)))-1)

The both formula you have posted here return the first value to meet the criteria. Thanks.

 

[Aladin Akyurek]

I pressed the wrong button to post my reply to Aladin. Aladin, thanks. Can you please explain why the lookup does not work in this case? I have found a solution to return the last value in another thread using sumproduct. I think that it was you to reply to the question.

INDEX(TEXT($Q$2:$Q$366,"dd mmm yyyy"),SUMPRODUCT(MAX((INDEX(T2:T9,S1)=$R$2:$R$366)*ROW($R$2:$R$366)))-1)

I don't combine SumProduct and Max... LOOKUP requires a sorted vector as the match-range. If the look up value it's fed with is not available and it is > that the last value in its match-range, it will deliver its last value from that range. That, exactly as designed for.

The both formula you have posted here return the first value to meet the criteria. Thanks.

Don't they solve your problem?

 

[agasi]

Thanks. I was looking for finding last date of that week. Sorry that it was not you but the sumproduct works to return what i was looking for for now.

Regarding look up, there is a matching value and it is not greater than last value in range. I still dont understand why lookup doesnt work anymore. I have been using the lookup formula for last few months and it been good.