Using vlookup to extract data from closest date and matching criteria

[ShadowLady17]

Hi, would appreciate any help on this please.

My data is like this:

Worksheet 1:
date from | date to | address

Worksheet 2:
address | date from | date to | occupancy (occupied or vacant)

I need to do a vlookup from the address on worksheet 1 to 2, finding the date when it was last occupied as close to the date from worksheet 1 as possible, for example:

Worksheet 1:

ST4 2LN

01/04/2022​

31/03/2023​

Worksheet 2:

ST4 2LN	03/05/2022	31/12/9999	Occupied
ST4 2LN	12/04/2022	02/05/2022	Vacant
ST4 2LN	24/11/2021	11/04/2022	Vacant
ST4 2LN	19/11/2021	23/11/2021	Vacant
ST4 2LN	18/11/2021	18/11/2021	Vacant
ST4 2LN	09/11/1992	17/11/2021	Occupied
ST4 2LN	23/10/1992	08/11/1992	Vacant

I would need the row that's highlighted - the 2 dates and the occupancy.

 

[ExceLoki]

on worksheet1 there are 2 dates, does it matter which date you are comparing to on sheet 2?