Complex Date and Address Problem

Dear Jay Virdee,

Kindly refer to the formulas below for

1. Finding the Address on the particular Date. (Cell H2, coloured Green)

=INDEX($B$1:$B$10000,MATCH(F2&" "&MAX(IF($C$1:$C$10000<=$G2,$C$1:$C$10000,0)*IF($A$1:$A$10000=$F2,1,0)),$A$1:$A$10000&" "&$C$1:$C$10000,0),1)

2. Finding the 'TO' Date. (Cell H2, coloured Yellow)

=MIN(IFERROR(IF($C$2:$C$100>C2,$C$2:$C$100,"-")*IF($A$2:$A$100=A2,1,"-")-1,TODAY()))

Notes:

1. The formulas work correctly even when the dates are not sorted.

2. Both the formulas are Array Formulas. They need to be entered using Control + Shift + Enter, instead of Enter.

3. Change the cell references as required. Refer to the attached photograph.

4. To find the address on a particular Date, I have not used the 'To' Date. All dates till the next change of address for the particular ID have been considered to be at the previous address.

5. For the last entry of date for each ID, Today() has been used.

Vijaykumar Shetye,

Spreadsheet Excellence,

Panaji, Goa, India