formula to find second cell containing data

[doropeter]

Hi!

I am looking for a formula which will find the second cell in a row that contains information (which is not empty).
I have a table with over 30.000 rows, and 80 columns. Cells contain data in text format. Many of the cells are empty cells. In each row I have to find the second cell which is not empty.

 

[fairwinds]

Hi,

Try:

=INDEX(1:1,SMALL(IF(1:1<>"",COLUMN(1:1)),2))

confirmed with Ctrl + shift + enter.

 

[doropeter]

Hi,
thanks for the idea, but the above formula does not give the result that I expected. I am still working on it.

 

[fairwinds]

Did you confirm the formula with Ctrl + shift + enter and not just with enter?

Book1
	A	B	C	D	E	F	G	H	I
1			First entry			Second entry		Third entry
2
3
4
5	Second entry
6
Sheet3

 

[doropeter]

Hi,

thanks, it works.

I highly appreciate your help.

 

[doropeter]

Hi,

thanks, it works.

I highly appreciate your help.