So I am creating a spreadsheet about who took care of which animal last when compared to a certain date. I am having an issue that when I look too far ahead, it won't see anyone's names, when I want to try to find the last care taker.
Example:
Years were changes dramatically to avoid confusion if dates are too similar.
<tbody>
</tbody>
My current code is:
=IFERROR(INDEX(C3:C8,MATCH(A3&MIN(IF((A3:A8=A3)*(C3:C8<>""),IF(B3:B8>B1,B3:B8))),A3:A8&B3:B8,0))&"","-")
So If I enter today's date in B2 (3/29/17), it should return Bob as the last caretaker. Today is 3/29/17 and the last date is 2/3/17, but no name is recorded, so Bob was the last care taker.
If I enter 10/5/19, it returns Bill, since that was the last care taker. The last recorded name was Bill.
If I enter 4/10/17, the name returned will be Chris.
When the column is blank, I do not get a return and I need to return the name before the previous blank.
Any help is appreciated
Example:
Years were changes dramatically to avoid confusion if dates are too similar.
Date: | (Enter any date here) | |
Animal | Date | Name |
Cat | 1/2/16 | Bob |
Cat | 2/3/17 | (blank) |
Cat | 4/5/17 | Chris |
Cat | 5/8/18 | Bill |
Cat | 7/10/18 | (blank) |
Cat | 9/12/19 | (blank) |
<tbody>
</tbody>
My current code is:
=IFERROR(INDEX(C3:C8,MATCH(A3&MIN(IF((A3:A8=A3)*(C3:C8<>""),IF(B3:B8>B1,B3:B8))),A3:A8&B3:B8,0))&"","-")
So If I enter today's date in B2 (3/29/17), it should return Bob as the last caretaker. Today is 3/29/17 and the last date is 2/3/17, but no name is recorded, so Bob was the last care taker.
If I enter 10/5/19, it returns Bill, since that was the last care taker. The last recorded name was Bill.
If I enter 4/10/17, the name returned will be Chris.
When the column is blank, I do not get a return and I need to return the name before the previous blank.
Any help is appreciated
Last edited: