Excel formula to find which of the matching rows has the latest Date, then update other rows with found value

Question

A few requirements here. If you can imagine this sample table would be thousands of rows with varying Project Number values.

What I'm trying to do is use a formula that will do the following:

1. Find all rows with the same Project Number.
2. Of those matching rows, find which has the latest Date.
3. Grab the value of column 2 from the row with latest date.
4. Update the rows found in step 1 so that column 4 equals the value found in step 3.

Sample Table before formula

Desired outcome after formula

question from:https://stackoverflow.com/questions/65932201/excel-formula-to-find-which-of-the-matching-rows-has-the-latest-date-then-updat

Answer 1

With Excel365 you can use below formula.

=INDEX(SORT(FILTER($A$2:$C$6,$A$2:$A$6=A2),3,-1),1,2)

For non O365 user try below array formula.

=INDEX($B$2:$B$6,MATCH(MAX(IF($A$2:$A$6=A2,$C$2:$C$6,"")),$C$2:$C$6,0))

Press CTRL+SHIFT+ENTER to evaluate the formula as it is an array formula.