How to calculate public holidays payable hours

I am trying to write an excel formula that categorises the information on each date row as being a public holiday (PH), weekend (WE) or weekday (WD). Public holidays should display as PH regardless of the day they fall on. Days should only be categorised as WE or WD if they are not public holidays.

Here is the sample dataset.

I also need to write a formula to insert hours into column F(PH Payable hours) if 1) It is a Public holiday AND (2) the employee has worked at least 3 out of the last 4 like days. The number of hours inserted must be the average of the prior like days.

For an example : If the Public holiday falls on a Monday refer to hours worked on the previous 3 or 4 Mondays.

This is one of my look up table to find out PH

I have written this formula but unable to populate PH in "Date category" column

=IF(COUNTIF(H52:I57, B54) > 0, "PH", IF(WEEKDAY(B54, 2) > 5, "WE", "WD"))

Any help would be apprecaited.

(1) Your formula is OK, just need to put anchors in so you can copy it down:

=IF(COUNTIF(H$2:I$7, A2) > 0, "PH", IF(WEEKDAY(A2, 2) > 5, "WE", "WD"))

(2) If you have Excel 365, you can filter the data to include only days which fall on the same weekday, are earlier than the current day and are themselves working days then average the last four ( or three if only 3/4 have been worked):

=IFERROR(
LET(range,A$2:E$14,
Date,A$2:A$14,
class,E$2:E$14,
prevDays,FILTER(range,(WEEKDAY(Date)=WEEKDAY(A2))*(Date<A2)*(class="WD")),
last4,CHOOSECOLS(TAKE(SORT(prevDays,1,-1),4),3),
worked,SUM(--(last4>0)),
IF(worked>=3,AVERAGE(last4),"")),
"")