coverage lookup based on date range

Hello experts,

I need your help in writing a lookup formula/array that could satisfy this requirement.

We have 2 traders in this scenario, first trader - X7 always holds his stocks for period of 7 days and oter trader - Y3 always holds his positions for 3 calender days. for example from sample sheet, X7 bought AAPL on January 1st and sold his position on Jan 7th. He bought another lot of AAPL on Jan 4th and sold after 7 days on 10th Jan. Similarly Trader Y3 bought AAPL on 2nd Jan and sold after 3 days on 4th Jan then bought firstr lot of GOOGL on 3rd Jan till 5th Jun , total of 3 days and so on

My requirmeent is, i want to check what is the overlap of trader Y3 position within trader X7s postiion. For example Y3 held AAPL between 2nd and 4th Jan, which overlaps 100% in X7's first purchase of AAPL from 1st Jan and 7th Jan and overlaps partially(1 day) with his position between 4th Jan to 10th Jan(4th Jan being common). The FB lot that trader Y3 bought between 21-23rd Jan doesnt overlap in X7 range of 24th-30th Jan and so on

The overlap requirement is only on one side, ie what percenatge of X7 holdings were also present in Y3 portfolio for same stock during same period, 100% overlap, partial or 0%

The original list contains more than 10k records so only made a sample here. Please let me know how this can be achieved with excel formulas.

TIA

Hi Ali, Thanks for pointing that out

I have included the mock up results, the values i want to be calculated is one of 3 overlap values - "FULL", "PARTIAL" or "NONE"

Let me know if it is sufficient

Thanks JeteMc, That was exactly as i wanted