Formula need to pick the adjacent header of last value greater than Zero

[Negi1984]

Hi All,

I have some data from column R to column CZ. Row 1 contains the Headers.
Now I need to look for last non-zero value and pick the header next that cell.

I am using the below formula right now but its not giving me the next header.
its giving me the header of cell where last non zero value exist.

=LOOKUP(2,1/(R2:CW2<>0),$R$1:$CW$1)
Please suggest, what changes I can do in the above formula. or any other formula to solve the issue.

Regards,
Rajender

 

[Negi1984]

Hi Aladin,

Thanks a ton for helping me out to solve the issue.
its working fine.

Have a nice day to you.

	A	B	C	D	E	F	G	H	I	J	K	L	M
1	Jan-14	Feb-14	Mar-14	Apr-14	May-14	Jun-14	Jul-14	Aug-14	Sep-14	Oct-14	Output	intermediate result	Result
2	1	0	0	0	0	0.25					Feb-14	100001####	Feb-14
3	1	1	1	1	1	1	1	1	1	1	#N/A	1111111111	#N/A
4	1	1			1	1	0	0	0	0	Jul-14	11##110000	Jul-14
5	1	1	1	1	1	1	1	1	1	1	#N/A	1111111111	#N/A
6	1	1	1	1	1	0	1	0	0	0	Aug-14	1111101000	Aug-14
7	1	1	1	1	1	0	0	0	0	0	Jun-14	1111100000	Jun-14
8	0	0	0	0	0	0	0	0	0	0	#N/A	0000000000	#N/A
9	1	1	1	1	1	1	0	0	0	0	Jul-14	1111110000	Jul-14
10	1	1	1	1	1	1	1	1	1	1	#N/A	1111111111	#N/A
11	1	0	0	0	0	0.25	0				Jul-14	1000010###	Jul-14
12	1	1	1	1	0	1	0	1			Jul-14	11110101##	Jul-14

<colgroup><col><col><col><col><col><col><col><col><col><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Install first the following udf Sreverse as module by means of Alt+F11...

Public Function Sreverse(Rng As Range)
Sreverse = StrReverse(Rng.Text)
End Function

In L2 control+shift+enter, not just enter, and copy down:

=TEXTJOIN("",TRUE,IF(ISNUMBER(A2:J2),IF(ABS(A2:J2)>0,1,0),"#"))

In M2 control+shift+enter, not just enter, and copy down:

=IFERROR(INDEX($A$1:$J$1,COLUMNS($A2:$J2)-LOOKUP(9.99999999999999E+307,FIND("01",Sreverse(L2)))+1),#N/A)

Note. If Sreverse can be modified to admit an array refernce, we can eliminate the intermediate result.

 

[Aladin Akyurek]

Hi Aladin,

Thanks a ton for helping me out to solve the issue.
its working fine.

Have a nice day to you.

You are very welcome. Happy to have your update.