Capture a list of data based on values from adjacent row within 1 cell for dropdown list

Hi all,

This is my first time posting a thread here. I have encountered a problem here which I found to be quite difficult to combine the formulae with.

Ultimately, I would like to move the final formula to data validation to produce a "Dependent drop-dwon list". Before I could produce the list, I would need a formula that could match the values from the adjacent row (i.e. below 1 row) to see whihc cell should be included or excluded from the drop-down list.

I have tried a various combinations of formulae such as index and match, Hlookup, Lookup, offset, IF , but all the formulae that I have used seems to have some bugs that do not give the correct results.

I have here attached a simple excel file that im working on.

May I get some assistance or suggestions on how to get a accurate result or that will make this working? Many thanks!!