Derive column value based on multiple criteria

I have the following data in excel. I would like to derive the "Action" column for a "Person ID" based on the Y or N indicators in the "Value" and "Month". I have also attached an excel file with this data.

Person ID Value Month Action
1 N 1/1/23 1st action
2 Y 1/1/23
1 N 2/1/23 2nd action
2 N 2/1/23 1st action
2 Y 3/1/23
3 N 3/1/23 1st action
2 N 4/1/23 1st action
3 Y 4/1/23
4 Y 4/1/23


For a Person ID, if the value is Y then action should be empty
If for a Person ID, the value is N then:
1. Check the last record for that person ID and whether the month is one prior to the current month
a. If a record exists for the person ID in the prior month, then check the value for that record
i. If the value is N then action column should be populated with "2nd action"
ii. If the value is Y then action should be "1st action"
b. If the person ID is not present in the prior month then action should be "1st action"
Once a person ID is flagged for "2nd action" that person's record will not be included in the next month

I have given some examples below:
1. For Person ID 1
a. In 1/1/23 since the value is N action is "1st action"
b. In 2/1/23 value was N and in the prior month (1/1/23) the value was also N, action is "2nd action"
2. For Person ID 2
a. In 1/1/23 since value is Y action is empty
b. In 2/1/23 since value is N and value in the prior month is Y, action is "1st action"
c. In 3/1/23 since value is Y action is empty
d. In 4/1/23 since value is N and value in 3/1/23 is Y then "1st action"
3. For Person ID 3
a. In 3/1/23 since value is N and there is no record for this ID in the prior month (2/1/23), action is "1st action"
b. In 4/1/23 since value is Y action is empty

Date format is mm/dd/yy

Thank you for offering help!

Cell D8 should have 1st action because previous month for that person ID is Y (row 6). Only when there are Ns for two consecutive months for a person ID should the action be 2nd action.

E2
=IF(B2="Y","",CHOOSE(SUMPRODUCT((A$2:A2=A2)*($B$2:B2="N")*(DATEDIF($C$2:C2,C2,"YM")<=1)),"1st ","2nd ","3rd ","4th ")&"action")

copied down.

Thank you everyone! windknife - your solution worked!

You are welcome.