Formula to tell the first non-blank in a range

[Rainbow_P]

Good day!

Can you please help me with a formula that can indicate the first instance of non-blank cell in a range?

Sample as screenshot, row #3 (B3:G3) has 2 numbers. A formula is needed to indicate D3 is the first instance of non-blank in the range.

I've tried =SUMPRODUCT(--($B$3:G3<>""))=0, but not really.

Thank you.

 

[bosco_yip]

In H2, formula copied down :

=INDEX(B2:G2,INDEX(MATCH(1,B2:G2^0,),))

 

[Rick Rothstein]

A cell cannot have both a constant value and a formula... it is one or the other. If, based on the two pictures you posted, cell E2 has the number 21 in it, then you cannot put a formula in that cell to display the word "FIRST".

 

[Rainbow_P]

In H2, formula copied down :

=INDEX(B2:G2,INDEX(MATCH(1,B2:G2^0,),))

@bosco_yip , thank you for your help.
sorry for the confusion - I meant, to have another correspondent 6 new columns (in addition to the originals A to G) to indicate the where the "First".

 

[Rainbow_P]

A cell cannot have both a constant value and a formula... it is one or the other. If, based on the two pictures you posted, cell E2 has the number 21 in it, then you cannot put a formula in that cell to display the word "FIRST".

@Rick Rothstein , thank you for your reply.

sorry for the confusion - I meant, to have another correspondent 6 new columns (in addition to the originals A to G) to indicate the where the "First". Like:

 

[kevin9999]

Something along these lines?

tally.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	2021-11	2021-12	2022-01	2022-02	2022-03	2022-04		Name	2021-11	2021-12	2022-01	2022-02	2022-03	2022-04
2	Harper				21				Harper				First
3	Evelyn			15			7		Evelyn			First
4	Ben			8		17	21		Ben			First
5
6	Name	2021-11	2021-12	2022-01	2022-02	2022-03	2022-04
7	Harper				First
8	Evelyn			First
9	Ben			First
10
Sheet3

Cell Formulas
Range	Formula
J2:J4	J2	=IF(AND(COUNTA(A2:$B2)=0,B2<>""),"First","")
K2:O4	K2	=IF(AND(COUNTA($B2:B2)=0,C2<>""),"First","")
B7:B9	B7	=IF(AND(COUNTA(A2:$B2)=0,B2<>""),"First","")
C7:G9	C7	=IF(AND(COUNTA($B2:B2)=0,C2<>""),"First","")

 

[kevin9999]

Just noticed the first column formula needs to be different, so:

tally.xlsb
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1	Name	2021-11	2021-12	2022-01	2022-02	2022-03	2022-04		Name	2021-11	2021-12	2022-01	2022-02	2022-03	2022-04
2	Harper				21				Harper				First
3	Evelyn			15			7		Evelyn			First
4	Ben			8		17	21		Ben			First
5
6	Name	2021-11	2021-12	2022-01	2022-02	2022-03	2022-04
7	Harper				First
8	Evelyn			First
9	Ben			First
10
Sheet3

Cell Formulas
Range	Formula
J2:J4	J2	=IF(B2<>"","First","")
K2:O4	K2	=IF(AND(SUM($B2:B2)=0,C2<>""),"First","")
B7:B9	B7	=IF(B2<>"","First","")
C7:G9	C7	=IF(AND(COUNTA($B2:B2)=0,C2<>""),"First","")

 

[Rainbow_P]

Just noticed the first column formula needs to be different, so:

Brilliant, @kevin9999 , thank you for the help and aspiration!

Such a beautiful logic that others can also learn from this.

Wish you a great weekend!

 

[kevin9999]

Brilliant, @kevin9999 , thank you for the help and aspiration!

Such a beautiful logic that others can also learn from this.

Wish you a great weekend!

Glad we could help, and thanks for the feedback (please note post #7 is the one you should use).

 

[Peter_SSs]

If you want, there should be no need to have different formulas - assuming numerical values in the rows as per your sample.

22 05 07.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	Name	2021-11	2021-12	2022-01	2022-02	2022-03	2022-04		Name	2021-11	2021-12	2022-01	2022-02	2022-03	2022-04
2	Harper				21				Harper				First
3	Evelyn			15			7		Evelyn			First
4	Ben			8		17	21		Ben			First
5	Ann								Ann
6	Tom	5		4		3			Tom	First
First

Cell Formulas
Range	Formula
J2:O6	J2	=IF(AND(COUNT($B2:B2)=1,COUNTIF($I2:I2,"?*")=1),"First","")