Find last zero value + 1 column and output from another row in the same column

[HypnoHans]

Hi everyone,

I have some issues getting the output that I want in relation to the following example:

F G H I
Row 1: jan 22 feb 22 mar 22 apr 22

Row 200: 2 0 0 4


I need a formula capable of finding the last zero value within a range, + 1 column (so in the example above the result would be 4 (Cell I200). Then I need the formula to, based on the column of the value, output the value from Row 1 of the same column (in the example above the output would be apr 22 (Cell I1)).

Maybe it is a bit confusing, but I hope that you guys can help.

 

[Toadstool]

Hi HypnoHans,

You don't say what to do if there are no zeros or the last zero is in the last column so take a look at this and comment.

HypnoHans.xlsx
	F	G	H	I	J	K
1	Jan-22	Feb-22	Mar-22	Apr-22	Last One	Result
2	0	0	0	0		Last One
3	1	0	0	2		Apr-22
4	0	1	1	1		Feb-22
5	1	0	0	0		Last One
6	2	0	0	4		Apr-22
7	1	2	3	4		No zeros
8	1	0	2	3		Mar-22
Sheet1

Cell Formulas
Range	Formula
G1:I1	G1	=EOMONTH(F1,0)+1
K2:K8	K2	=IFERROR(INDEX($F$1:$J$1,AGGREGATE(14,6,COLUMN($F$2:$I$2)-COLUMN($E$2)/($F2:$I2=0),1)+1),"No zeros")

 

[HypnoHans]

Hi HypnoHans,

You don't say what to do if there are no zeros or the last zero is in the last column so take a look at this and comment.

HypnoHans.xlsx
	F	G	H	I	J	K
1	Jan-22	Feb-22	Mar-22	Apr-22	Last One	Result
2	0	0	0	0		Last One
3	1	0	0	2		Apr-22
4	0	1	1	1		Feb-22
5	1	0	0	0		Last One
6	2	0	0	4		Apr-22
7	1	2	3	4		No zeros
8	1	0	2	3		Mar-22
Sheet1

Cell Formulas
Range	Formula
G1:I1	G1	=EOMONTH(F1,0)+1
K2:K8	K2	=IFERROR(INDEX($F$1:$J$1,AGGREGATE(14,6,COLUMN($F$2:$I$2)-COLUMN($E$2)/($F2:$I2=0),1)+1),"No zeros")

Hi Toadstool,

It works - thank you!

 

[Toadstool]

Hi Toadstool,

It works - thank you!

You're welcome!

 

[HypnoHans]

Hi @Toadstool

Quick question, which I hope that you can help clarify!

Is it possible to do a formula that simply finds the first value that is < 0 in a range, and outputs the corresponding value from row 1 (Like in the example/solution above).

So for instance:

	Feb-22	Mar-22	Apr-22
1	1	-5	-8
2	-10	2	-20

The output from the formula of row 1 should be: Mar-22
The output from the formula of row 2 should be: Feb-22

I hope that you can help.

 

[AlanY]

try this

Book1
	A	B	C	D	E	F
1		Feb/22	Mar/22	Apr/22
2	1	1	-5	-8		Mar/22
3	2	-10	2	-20		Feb/22
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX($B$1:$D$1,MATCH(TRUE,B2:D2<0,0))

 

[HypnoHans]

try this

Book1
	A	B	C	D	E	F
1		Feb/22	Mar/22	Apr/22
2	1	1	-5	-8		Mar/22
3	2	-10	2	-20		Feb/22
Sheet1

Cell Formulas
Range	Formula
F2:F3	F2	=INDEX($B$1:$D$1,MATCH(TRUE,B2:D2<0,0))

Thanks!

 

[HypnoHans]

@AlanY @Toadstool

Sorry... but I've got one more questions, which is simply can't figure out....

I need to find the first positive number after negative number(s) and output the corresponding value from the same column in row 1.

Example:

	F	G	H	I	J
1	feb 22	mar 22	apr 22	may 22	jun 22
2	10	5	-55	-25	14

So in this example the output of the formula should be jun 22, as the first positive value, after a negative value is 14 (J2).

I hope that you can help.