Developer Forum

I have what is, essentially, a real estate problem. I am trying to use a
function to find the previous owner for a given property. The formula that I
am using is below, but it returns the first owner for that property, not the
owner previous to the selected owner.
Owners are assigned unique numbers. I have found the first date that a
given owner owned a property, and I want to find who owned that property
before them. For the formula below, the following information is required
for it to make sense:

Sheet 1
Column R contains the name of the property-a unique letter and number code
assigned to each property.
Column S contains the date that the new owner moved in.

MIDDATE
Column G contains the owners' names-a unique number assigned to each
individual.
Column I contains the property names
Column J contains the dates (census dates and selling dates) that it was
owned by each owner.

Example: in Sheet1 the formula in V12 is
{=INDEX(MIDDATE!$G$2:$G$21624,MATCH(1,(MIDDATE!$I$2:$I$21624=R12)*(MIDDATE!$J$2:$J$21624<S12),0))}

I'm sure there's a simple solution, but I can't figure it out. I tried just
sorting the MIDDATE sheet by date descending instead of ascending, but it
didn't work. I'm not very experienced with Excel in general, and certainly
not with array functions. I hope I've included all the necessary information
here. Please ask if I can make it any easier for someone to help me, I've
been trying to do this for two days.
Thanks in advance

Try

=INDEX(MIDDATE!$G$2:$G$21624,MATCH(1,(MAX((MIDDATE!$I$2:$I$21624=R12)*(MIDDATE!$J$2:$J$21624<S12))=MIDDATE!$J$2:$J$21624)*(MIDDATE!$I$2:$I$21624=R12),0))