Search code examples
excelexcel-formula

Excel Latest Date Summary

I've got a table with dates going down and players going across, like this:

enter image description here

How can I write a formula for another table with names going down, and a Latest Date column based on if the table above is filled in for that date for that player? For example Player 1 has something filled in for both dates and so it'll just be the max date, whereas Player B only had 1 date filled in, so his max date is different.

enter image description here

Solution

  • Perhaps you could try something along the lines of:

    enter image description here

    • Formula used in cell F2 (Ensure to fill down and change the ranges accordingly per your data.)

    =MAX((FILTER(B$2:C$3,B$1:C$1=E2)<>"")*A$2:A$3)

    • Also, can spill the output with one single array formula like as below using a LAMBDA() helper function MAP():

    =MAP(E2:E3,LAMBDA(x,MAX((FILTER(B2:C3,B1:C1=x)<>"")*A2:A3)))

    • And using MAXIFS() with dynamic approach:

    =MAXIFS(A:A,INDEX(B:C,,XMATCH(E2,B$1:C$1)),"<>")