If I am correct, we need to match the status of a particular address given a specified date. In this case, it would be best to have continuous status codes in the table that we have so that we can match using the INDEX and MATCH functions.
If we wanted to know the status of the row 4 (3 My Street) at 4/30/2015 we would want to have cell F4 as code "s" as it is follows on from E4. Then we can grab the value using the MATCH on the date and INDEX on the code row. Since F1 is the date we require, the formula is:
=INDEX(C4:G4,MATCH(F1,C1:G1,0))
The MATCH gets the offset 4 from the dates which is then used in the index of codes to return the correct status. We can also use different match types (less than, greater than) for when the dates do not match exactly.
