Choose Max Date

[Pestomania]

Hello,

I am trying to get the maximum "End Date" for a WT & Step # combo. My "Lookup Field" is the left column and I am using the following formula to lookup the end date, but it only pulls the first record in the list, I want to get the maximum date from the list.

How can I modify this formula to give me that?

VLOOKUP("000000021607-23",ScanHist[[LookupField2]:[End Date]],11,FALSE)

LookupField2	WTNumber	TransactionDate	StartDate	StartTime	Start	Start Date	EndDate	End	EndTime	End Date
000000021607-10	000000021607	44792	20220819	8.5	08/19/2022	8/19/22 8:30 AM	20220819	08/19/2022	8.75	8/19/22 8:45 AM
000000021607-20	000000021607	44805	20220901	11.4	09/01/2022	9/1/22 11:24 AM	20220901	09/01/2022	15.4	9/1/22 3:24 PM
000000021607-23	000000021607	44792	20220819	10.43333	08/19/2022	8/19/22 10:26 AM	20220819	08/19/2022	10.53333	8/19/22 10:31 AM
000000021607-23	000000021607	44796	20220823	7.15	08/23/2022	8/23/22 7:09 AM	20220823	08/23/2022	15.06667	8/23/22 3:04 PM
000000021607-23	000000021607	44797	20220824	7.16667	08/24/2022	8/24/22 7:10 AM	20220824	08/24/2022	15.23333	8/24/22 3:13 PM
000000021607-23	000000021607	44798	20220825	7.15	08/25/2022	8/25/22 7:09 AM	20220825	08/25/2022	10.56667	8/25/22 10:34 AM
000000021607-23	000000021607	44805	20220901	7.73333	09/01/2022	9/1/22 7:44 AM	20220901	09/01/2022	12.13333	9/1/22 12:07 PM
000000021607-26	000000021607	44805	20220901	11.45	09/01/2022	9/1/22 11:27 AM	20220901	09/01/2022	15.45	9/1/22 3:27 PM
000000021607-40	000000021607	44816	20220912	5.96667	09/12/2022	9/12/22 5:58 AM	20220912	09/12/2022	6.46667	9/12/22 6:28 AM
000000021607-70	000000021607	44812	20220908	5.9	09/08/2022	9/8/22 5:54 AM	20220908	09/08/2022	13	9/8/22 1:00 PM
000000021607-70	000000021607	44816	20220912	6.56667	09/12/2022	9/12/22 6:34 AM	20220912	09/12/2022	6.56667	9/12/22 6:34 AM
000000021607-80	000000021607	44834	20220930	14.45	09/30/2022	9/30/22 2:27 PM	20220930	09/30/2022	14.7	9/30/22 2:42 PM

 

[Joe4]

I think that MAXIFS function may do what you want.
See: MAXIFS function - Microsoft Support

 

[Tom Urtis]

Maybe
=MAX(IF(A2:A100="000000021607-23",C2:C100))

Be sure to commit that formula to the cell with Ctrl+Shift+Enter, not just with Enter.
Also, I see you used named ranges which I never use, so adjust my cell references accordingly if this formula (again, with Ctrl+Shift+Enter) works as you want.

 

[Pestomania]

Maybe
=MAX(IF(A2:A100="000000021607-23",C2:C100))

Be sure to commit that formula to the cell with Ctrl+Shift+Enter, not just with Enter.
Also, I see you used named ranges which I never use, so adjust my cell references accordingly if this formula (again, with Ctrl+Shift+Enter) works as you want.

Hi tom,

This works great, if there is a record to find. I am finding that if 21607 exists anywhere with a step number (even if it is a different step number), it returns a value of 1/0/00 12:00 am. If 21607-23 does not exist, it should return a value of "". Can that be coded?

I tried: =MAX(IF(A2:A100="000000021607-23",C2:C100,"")) but that did not work.

 

[Joe4]

it returns a value of 1/0/00 12:00 am

That is just zero, formatted as a date.

Try something like this, using the MAXIFS function I suggested, which does NOT require you to use CNTRL+SHIFT+ENTER:

=LET(x,MAXIFS(C2:C13,A2:A13,"000000021607-23"),IF(x=0,"",x))

 

[Fluff]

Another option

=TAKE(SORT(FILTER(ScanHist[End Date],ScanHist[LookupField2]="000000021607-23",""),,-1),1)

 

[Pestomania]

That is just zero, formatted as a date.

Try something like this, using the MAXIFS function I suggested, which does NOT require you to use CNTRL+SHIFT+ENTER:

=LET(x,MAXIFS(C2:C13,A2:A13,"000000021607-23"),IF(x=0,"",x))

This worked like a perfect dream! Thank you very much @Joe4 !

 

[Joe4]

You are welcome.
Glad we were able to help!