minifs; grab data in column for last available data

[vbaisdaxing]

date	amount
1/1/15	1
1/2/15	2
1/3/15	3
1/4/15	4
1/5/15

<tbody>
</tbody>

Hello everyone,

Assuming that the table starts at cell A1, this is what i am trying to accomplish:

I have excel 2016 and utilized the MINIFS formula and was able to grab 1/15/15 date by using =MINIFS(A2:A6,B2:B6,"")

what i'm trying to do is when there is a blank cell, i want to grab the date/data in the previous cell. In this case I would like to grab 1/4/15.

any suggestions or other methods would be greatly appreciated. Thank you.

 

[jtakw]

Hi,

This should work:

Excel 2010
	A	B	C	D
1	date	amount		1/4/2015
2	1/1/2015	1
3	1/2/2015	2
4	1/3/2015	3
5	1/4/2015	4
6	1/5/2015
Sheet10

Cell Formulas
Range	Formula
D1	=INDEX(A2:A6,MATCH(9.99999999999999E+307,B2:B6,1))

 

[vbaisdaxing]

thank you very much sir. Greatly appreciated!!!

 

[jtakw]

You're welcome.