Simple formula that is driving me mad

[churmie]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Sales[/TD]
[TD]Closing Stock[/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]12/Dec[/TD]
[TD]25[/TD]
[TD]75[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]13/Dec[/TD]
[TD]30[/TD]
[TD]45[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]14/Dec[/TD]
[TD]45[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]15/Dec[/TD]
[TD]60[/TD]
[TD]-160[/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD]Opening Stock[/TD]
[TD]100[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD]Runout[/TD]
[TD]????[/TD]
[TD]Needs to return 14/Dec[/TD]
[/TR]
</tbody>[/TABLE]

How do I get cell B8 to return the date 14/Dec as the date stock runs out? I have used IF formula but I am doing something wrong?

Thanks in advance...

Churmie

 

[AlanY]

try this, confirm with shift-enter-return

=INDEX(A2:A5,MATCH(SMALL(IF(C2:C5>=0,C2:C5),1),C2:C5,0))

 

[steve the fish]

Try:

=INDEX(A2:A5,MATCH(TRUE,INDEX(C2:C5<=0,0),0))

 

[Finalfight40]

You can get it with something like this:

=INDEX(A2:A5, MATCH(0, C2:C5, 0))

But it might just help to elaborate on if you are going to get more of this stock in the future and add it onto the bottom of this list?

 

[churmie]

You can get it with something like this:

=INDEX(A2:A5, MATCH(0, C2:C5, 0))

But it might just help to elaborate on if you are going to get more of this stock in the future and add it onto the bottom of this list?

Excellent! I thought it might be an index question, thank you all all!

 

[steve the fish]

That one is the least robust. It only works if your closing stock goes down to exactly zero. It appears to me your stock can go negative so i wouldnt use that one.

 

[Finalfight40]

That one is the least robust. It only works if your closing stock goes down to exactly zero. It appears to me your stock can go negative so i wouldnt use that one.

This is true. If this is the case then i would also recommend using one of the others provided as i was only attempting to provide when the closing stock hits exactly 0.

 

[AlanY]

this is not as simple as first look, let say you've 10 left over on the 14th, formula above yield different results

Book1
	A	B	C
1		Sales	Closing Stock
2	12-Dec	25	75
3	13-Dec	30	45
4	14-Dec	35	10
5	15-Dec	60	-50
6
7	Opening Stock	100
8	Runout	14-Dec
9		15-Dec
10		#N/A
Sheet1

Cell Formulas
Range	Formula
B9	=INDEX(A2:A5,MATCH(TRUE,INDEX(C2:C5<=0,0),0))
B10	=INDEX(A2:A5, MATCH(0, C2:C5, 0))
B8	{=INDEX(A2:A5,MATCH(SMALL(IF(C2:C5>=0,C2:C5),1),C2:C5,0))}
Press CTRL+SHIFT+ENTER to enter array formulas.