Finding last cell that is not blank

[toongal12]

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.

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

 

[Weazel]

maybe something like...

E2=LOOKUP(9.99E+307,1/((C3:C8<>"")*(B3:B8<=B1)),C3:C8)

Row\Col	A​	B​	C​	D​	E​
1​	Date:	3/29/2017​
2​	Animal	Date	Name		Bob
3​	Cat	1/2/2016​	Bob
4​	Cat	2/3/2017​
5​	Cat	4/5/2017​	Chris
6​	Cat	5/8/2018​	Bill
7​	Cat	7/10/2018​
8​	Cat	9/12/2019​

<tbody>
</tbody>

 

[toongal12]

maybe something like...

E2=LOOKUP(9.99E+307,1/((C3:C8<>"")*(B3:B8<=B1)),C3:C8)

Row\Col	A​	B​	C​	D​	E​
1​	Date:	3/29/2017​
2​	Animal	Date	Name		Bob
3​	Cat	1/2/2016​	Bob
4​	Cat	2/3/2017​
5​	Cat	4/5/2017​	Chris
6​	Cat	5/8/2018​	Bill
7​	Cat	7/10/2018​
8​	Cat	9/12/2019​

<tbody>
</tbody>

I try to avoid lookup/vlookup because columns could be added/removed/changed

 

[Weazel]

why not put your data in a table to accomadate the adding and removing of columns and rows ?

 

[toongal12]

why not put your data in a table to accomadate the adding and removing of columns and rows ?

Data is in a table. INDEX-MATCH works for me and I'm more comfortable with it

 

[Weazel]

your formula syntax doesn't appear to represent an excel table.

it would look something like this

=LOOKUP(9.99E+307,1/((Table1[Name]<>"")*(Table1[Date]<=B1)),Table1[Name])

 

[toongal12]

your formula syntax doesn't appear to represent an excel table.

it would look something like this

=LOOKUP(9.99E+307,1/((Table1[Name]<>"")*(Table1[Date]<=B1)),Table1[Name])

For ease of understanding, it is recreated dummy data.

Will INDEX-MATCH not work?

 

[toongal12]

I just realized I worded the question in the opposite direction completely. I want to try the find the next listing, not the previous. I cannot edit the first post to change this

 

[toongal12]

I just realized I worded the question in the opposite direction completely. I want to try the find the next listing, not the previous. I cannot edit the first post to change this

My code that works finds the next Name according to the date. If one does not exist, it should find the previous name