asset allocation to a date period

Good day to everyone.

I am trying to find out how I can assign an assets to a company which is related to a date period.
I got two data sets which I separated in two sheets.

The 1st sheet “Company assignment” contains following information:
Column A + B = name the code and an asset name. it will always be the same code for one asset and therefore not change.
Column C = is manually adjusted when we move an asset to a different company.
Column D = will also be entered at the same time when an asset will be allocated to a different company.
Column E = should contain a date which is named in column F. I need a formula which I unfortunately is not so easy. For me at least. 

The 2nd sheet “Date and costs” contains following information:
Column A = a date when amounts have been booked. Same come with: Column B + C + D.
Column B = amount info
Column C = code info
Column D = asset name info
Column E = should contain a formula which in the end show the relevant company the asset was assigned to at the date given in column A. Which means the formula should consider the date and code. In case there is no ending date, then the latest company should be named.
Column F = The company which should pop up.

I hope this is possible within excel.

There will also be budget bookings, therefore future dates will be possible.

Many thanks for your help in advanced.

Brgds
Kai

Many thanks dear Metoo7,

sorry for my late reply,

Regarding the "Date and Costs" sheet where the formula should spit out the correct Company I notices that for cell E6 the company is incorrect. It should read Company 3 since the 654321 small car was allocated on 01.11.2021 for company 3.
I am wondering why, because the formula is apparently working for cell E4 where the case is similar, just with a different code/date.

And I noticed that the formula does not calculate itself automaticall when I change the date entry. Can this be solved as well?

In any case, many thanks for your help already.

brgds

Thanks for your quick reply.

Unfortunately the formula is not working in my sheet. result is #NAME?

Sorry. Can you have a look and correct if required or send me the full excel?

many thanks again in advanced.

brgds
kai