Find first entry with zero and return corresponding date

[blue333]

Hi

I am trying to write a formula that returns the date from a row that corresponds to the first cell in another row that is the first zero. The following example will help explain what I am mean.

[TABLE="width: 15"]
<tbody>[TR]
[TD]Dates
[/TD]
[TD]Column 1
[/TD]
[TD]Column 2
[/TD]
[TD]Column 3
[/TD]
[TD]Column 4
[/TD]
[/TR]
[TR]
[TD]Jan 1
[/TD]
[TD]4
[/TD]
[TD]1
[/TD]
[TD]9
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Feb 1
[/TD]
[TD]5
[/TD]
[TD]0
[/TD]
[TD]3
[/TD]
[TD]3
[/TD]
[/TR]
[TR]
[TD]Mar 1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]7
[/TD]
[TD]0
[/TD]
[/TR]
[TR]
[TD]Apr 1
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[TD]0
[/TD]
[/TR]
</tbody>[/TABLE]

The expected results are as follows:
Column 1 - Mar 1
Column 2 - Feb 1
Column 3 - Apr 1
Column 4 - Mar 1

The values in columns 1 to 4 are not blank. There is a formula that returns a calculated value. I only care if the value is zero.

I thought that this match/index formula did the job

=INDEX($A$8:$A$184,MATCH(TRUE,INDEX((C8:C184=0),0,0)))

but it is always returning the last value (Apr 1).

Thanks in advance for your help.

 

[jtakw]

Hi,

Not sure if I understand correctly:

Book1
	A	B	C	D	E	F	G	H	I	J
1	Dates	Column 1	Column 2	Column 3	Column 4		Results
2	1-Jan	4	1	9	3		1-Mar	1-Feb	1-Apr	1-Mar
3	1-Feb	5	0	3	3
4	1-Mar	0	0	7	0
5	1-Apr	0	0	0	0
Sheet40

Cell Formulas
Range	Formula
G2	=INDEX($A2:$A5,MATCH(0,B2:B5,0))

G2 formula copied across.

 

[blue333]

thank you so much. it works!!

 

[jtakw]

You're welcome.