Excel Function Help

I have a Excel workBook with 2 sheets.


Sheet 1 contains 2 columns. And the data is like the below.

Activity code Status
P1 In Progress
P2 Completed
P3 In Progress
P1 completed
P2 in progress
P3 Yet to start

Activity codes will repeat here.


Sheet 2 contains

Activity code Status
P1
P2
P3

The status should be captured from sheet 1 based on the below logic.


The scenario is like this

If all are yet to start I have to return Yetto start

If all are completed I have to return completed

If any one is In Progress I have to return In Progress.

For Example, For P1 I have In Progress and Completed.In this case I have to return In Progress.

Any Help??

Thanks
Sk5567

Some questions to ********:

You have several lines in Sheet1 with P1, but in Sheet2 there will only be one P1 , the value of that should be the result of that comes out of the function, correct??

And for the function:
You have the possible states
"In Progress"
"Completed"
"Yet to start"

You say if only one is "Yet to Start" the result has to be "Yet to start" (easy), but for the other two you say the result will be "in Progress" or "Completed" if all the values for the PX are of that state. What should be the result if there are some of both states??