Lookup first value in row of cells

[daveprospects180]

Hi
I have a row of data (A2:X2) which contain some blank and some non-blank
cells, I want to put the first (left to right) value in a new cell on same
row Z. The value of cells are numerical to indicate a month (1 for Jan, 12
for Dec etc). Any help would be greatly appreciated.

Thanks

David

 

[Bernard Liengme]

=MIN(IF(A2:X2<>"",COLUMN(A2:X2)))
This is an array formula so commit it with CTRL+SHITF+ENTER not just ENTER
best wishes

 

[Bernard Liengme]

Ops, I had not finished !

=INDEX(A2:X2,,MIN(IF(A2:X2<>"",COLUMN(A2:X2))))
this is also an array formula
best wishes

 

[daveprospects180]

Hi Bernard
It works great but only if there is no data or columns to the left of the row.
My sheet has some columns to the left of the data I want to refer to and it
does not work if it is there.
I am using
=INDEX(E16:X16,,MIN(IF(E16:X16<>"",COLUMN(E16:X16))))
Columns A-D have text, text, number, text in their cells

David

 

[Bernard Liengme]

Since you are now starting in column E, we need to subtract 4 from the
column number
=INDEX(E16:X16,,MIN(IF(E16:X16<>"",COLUMN(E16:X16)))-4)
best wishes

 

[RagDyeR]

Some non-array formulas:

Will display the first text *or* numeric value in the range:

=INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<>0,),))

Will display the first numeric value in the range:

=INDEX(E16:X16,MATCH(TRUE,INDEX(ISNUMBER(E16:X16),),))
--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================


message Hi Bernard
It works great but only if there is no data or columns to the left of the
row.
My sheet has some columns to the left of the data I want to refer to and it
does not work if it is there.
I am using
=INDEX(E16:X16,,MIN(IF(E16:X16<>"",COLUMN(E16:X16))))
Columns A-D have text, text, number, text in their cells

David

 

[Bernard Liengme]

Much better than mine - need more coffee!

 

[daveprospects180]

Great thanks RagDyeR and Bernard.
one last thing if there is no data in the row I need a blank cell or a zero

David

 

[RagDyer]

Try this:

=IF(COUNTA(E16:X16)=0,INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<>0,),)),"")

 

[RagDyer]

TYPO !

Try this instead:

=IF(COUNTA(E16:X16)<>0,INDEX(E16:X16,MATCH(TRUE,INDEX(E16:X16<>0,),)),"")

 

[daveprospects180]

Your input much appreciated though, thanks
David

Much better than mine - need more coffee!
--
Bernard V Liengme
Microsoft Excel MVP
http://people.stfx.ca/bliengme
remove caps from email

 

[daveprospects180]

Don't worry about last comment, have now got solution.
Many thanks for your input RagDyeR, yours wins the day.

David

 

[RagDyeR]

You're welcome, and appreciate the feed-back.
--

Regards,

RD
-----------------------------------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
-----------------------------------------------------------------------------------------------

message Don't worry about last comment, have now got solution.
Many thanks for your input RagDyeR, yours wins the day.

David