I would like to have the value populated (B1) when data is entered into A1 based on the latest date. I have no idea how to incorporate a max function together with an Index Match.

See screenshot below.

CodePudding user response：

If you have Excel 365 you can do it using MAXIFS:

Formulas used to rectify strings to date:

=MID(A22,FIND(" ",A22) 1,3)  
=VLOOKUP(B22,$Q$81:$S$92,2,0)  
=DATEVALUE(SUBSTITUTE(SUBSTITUTE(A22,B22,C22)," ","."))  
=D22