How To Lookup Second Non-Zero Value And Return Corresponding Column Header In Excel?

[martin88]

Hi Guys,
I have a data as shown below. I am was able to get the first non-zero value, however I am unable to figure out how to get the second none zero value. See sample data below. I know it could be something pretty simple. The formula I used for the first one is as follow: =INDEX($B$1:$J$1,MATCH(TRUE,INDEX(B2:J2<>0,),0))

Anyone can assist? It will be greatly appreciated. (If you can also explain on how to work it out to get the third and fourth non-zero value would be awesome)
Thanks in advance

 

[Domenic]

Name	8/01/22	8/02/22	8/03/22	8/04/22	8/05/22	8/06/22	8/07/22	8/08/22	8/09/22	First Day	Second Day	Third Day	Fourth Day
John		2		3				1	3	8/02/22	8/04/22	8/08/22	8/09/22

Enter the following formula in K2, confirm with CONTROL+SHIFT+ENTER, and copy across:

=INDEX($B$1:$J$1,SMALL(IF(ISNUMBER($B$2:$J$2),COLUMN($B$2:$J$2)-COLUMN($B$2)+1),COLUMNS($K2:K2)))

Hope this helps!

 

[lrobbo314]

Another option.

Book3
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	8/1/2022	8/2/2022	8/3/2022	8/4/2022	8/5/2022	8/6/2022	8/7/2022	8/8/2022	8/9/2022		Day	1	2	3
2	John		2		3				1				8/2/2022	8/4/2022	8/8/2022
Sheet3

Cell Formulas
Range	Formula
M2:O2	M2	=INDEX($B$1:$J$1,AGGREGATE(15,6,(1/(($B$2:$J$2<>""))*(COLUMN($B$2:$J$2)-COLUMN($B$2)+1)),M1))

 

[martin88]

Thanks Guys, @Domenic @lrobbo314 , both works great. Appreciate the quick response.

 

[Peter_SSs]

If your headings are actual dates (numbers) as they appear to be and in chronological order as shown then you can access those values directly, rather than through INDEX/MATCH

22 08 26.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L
1	Name	1/08/2022	2/08/2022	3/08/2022	4/08/2022	5/08/2022	6/08/2022	7/08/2022	8/08/2022	9/08/2022	First	Second
2	John		2		3				1		2/08/2022	4/08/2022
Get Date

Cell Formulas
Range	Formula
K2	K2	=AGGREGATE(15,6,B1:J1/(B2:J2<>""),1)
L2	L2	=AGGREGATE(15,6,B1:J1/(B2:J2<>""),2)

.. or if you might want more than 2

22 08 26.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N
1	Name	1/08/2022	2/08/2022	3/08/2022	4/08/2022	5/08/2022	6/08/2022	7/08/2022	8/08/2022	9/08/2022	First	Second	Third	Fourth
2	John		2		3				1		2/08/2022	4/08/2022	8/08/2022
3	Ann	2		5	6		8	3			1/08/2022	3/08/2022	4/08/2022	6/08/2022
Get Date (2)

Cell Formulas
Range	Formula
K2:N3	K2	=IFERROR(AGGREGATE(15,6,$B$1:$J$1/($B2:$J2<>""),COLUMNS($K:K)),"")