Vlook up along with conditions

[arun.sj]

Hi Team,

i have a report in such a way that there are so many status against the same name along with dates. Status like in progress, dropped, enrolled, not completed, planned, completed..

Now in the second sheet i have the names and i am trying to get the final status against each names. if i am manually updating the final status i will do this - if there is a word called "Completed" against a name, the final staus is "Completed". If there is no word called "Completed" against the name in the entire sheet, the final status is "Not completed".

i also need the date if the final status is "Completed".

i am looking for formula that will give me the final status and the date.

Sample data is attached in which the 1st sheet is the report with different status and the 2 nd sheet is where i am trying to update the final status and date.

Kindly requesting to help.

[samba_ravi]

=IF(COUNTIFS(Dump!$A$2:$A$11,$A2,Dump!$C$2:$C$11,"Completed"),"Completed","Not Completed")
Try this in Cell C2 and Copy towards down

[arun.sj]

=IF(COUNTIFS(Dump!$A$2:$A$11,$A2,Dump!$C$2:$C$11,"Completed"),"Completed","Not Completed")
Try this in Cell C2 and Copy towards down

Siva - the formula worked perfectely as required.

Can you also please help me with a formula to find the date if the final status is "Completed"

[samba_ravi]

=IF($C2="Completed",LOOKUP(2,1/((Dump!$A$2:$A$11=$A2)*(Dump!$C$2:$C$11=$C2)),Dump!$D$2:$D$11),"")
Try this in D2 and copy towards down

[arun.sj]

=IF($C2="Completed",LOOKUP(2,1/((Dump!$A$2:$A$11=$A2)*(Dump!$C$2:$C$11=$C2)),Dump!$D$2:$D$11),"")
Try this in D2 and copy towards down

This formula worked

But i have a challange in the date.. if there are 2 status called "Completed" for the same person with two different completion dates, which date would the formula take from sheet 1?

i need the formula to take the latest date and ignore the old dates. is that possible to update the formula please.

[boopathiraja]

Hi Refer the attachment

[samba_ravi]

Last date will be taken as Completed Date