minifs; grab data in column for last available data

[vbaisdaxing]

[TABLE="width: 500"]
<tbody>[TR]
[TD]date[/TD]
[TD]amount[/TD]
[/TR]
[TR]
[TD]1/1/15[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]1/2/15[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]1/3/15[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]1/4/15[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]1/5/15[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

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.