Return latest Date according to specified Month

[thanksamillion101]

Hello all! I truly appreciate this forum and the help I have received!! I do spend alot of time in trying to figure out myself before approaching this site. It is when there has just been too much time taken out of my day that I finally post a thread for help. So, again thank you all for your time and knowledge, it is greatly appreciated! Another formula needed and thank you in advance!

I need a formula that will return the latest date according to indicated Month ("U2") abbreviation. There will be many repeated dates and not sure if that is the problem, I have other conditional formatting as well.

IFERROR(LOOKUP(4,1/(A2:A8=M2)/(C2:C8=O2)/(G2:G8=P2)/(TEXT(D2:D8,"mm")=TEXT("*"&U2&"*","mm")),D2:D8),"Err")

A

B

C

D

E

F

G

H

M

N

O

P

U

1​

Name

Zone

Driver

Exam Date

Class

ID

City

State

Name

PR

Driver

City

Month

Latest Date

2​

J, Smith

4​

AA125

1-Jun-19​

C

125FFE

Denver

CO

J, Smith

551

AA125

Denver

Jun

Err

3​

K, Terry

6​

AA457

5-Jun-21​

K

457DDE

Dallas

TX

K, Terry

3284

AA457

Dallas

4​

L, Johnson

9​

BB813

3-Jan-20​

E

813EGA

Los Angeles

CA

L, Johnson

6009

Los Angeles

5​

P, Wayne

6​

CC777

11-Apr-21​

E

777KKA

Ft. Worth

TX

P, Wayne

771

CC777

Ft. Worth

6​

R, Zapata

5​

DD888

18-Jun-19​

H

888LWN

OKC

OK

R, Zapata

7722

DD888

OKC

7​

J, Smith

4​

AA125

12-Jun-21​

C

125FFE

Denver

CO

8​

J, Smith

4​

AA125

15-Apr-22​

C

125FFE

Denver

CO

 

[Peter_SSs]

If the dates for each name increase down the column, then you could try this adaptation of your formula.

=IFERROR(LOOKUP(4,1/(A2:A8=M2)/(C2:C8=O2)/(G2:G8=P2)/(TEXT(D2:D8,"mmm")=U2),D2:D8),"Err")

If the dates could be mixed up, or in any case, you could also try this.

=IFERROR(AGGREGATE(14,6,D2:D8/(A2:A8=M2)/(C2:C8=O2)/(G2:G8=P2)/(TEXT(D2:D8,"mmm")=U2),1),"Err")