find first value in a row with value greater than 0

[Guest]

I need to search rows in a worksheet for the first cell with a value over 0
and return the corresponding column name to another worksheet. Then need to
find the last cell with a value and return the corresponding column name.

eg.
worksheet 1
Celery Carrots Yams Onions Radish
0 3 7 5 0

worksheet 2
A B
Carrots Onions

Is it possible? I tried lookup and hlookup, but returning #N/A.

You assistance is appreciated.

Thanks

 

[Bob Phillips]

=INDEX(1:1,1,MIN(IF(1:1<>0,COLUMN(1:1))))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--
HTH

Bob Phillips

(remove nothere from email address if mailing direct)

 

[Peo Sjoblom]

=MATCH(TRUE,A2:E2>0,0)


entered with ctrl + shift & enter will give you the column number counted
from A so in you example it would be 2 and then use index

=INDEX(A1:E1,MATCH(TRUE,A2:E2>0,0))



=LOOKUP(2,1/(1-(2:2="")),2:2)


this will return the last value in row 2 if by that we mean the right most
in row 2
--

Regards,

Peo Sjoblom

http://nwexcelsolutions.com

 

[SteveG]

First Value

=INDEX(Sheet3!$A$1:$E$1,MIN(IF(Sheet3!$A$2:$E$2>0,COLUMN(Sheet3!$A$1:$E$1)-COLUMN(Sheet3!$A$1)+1)))

This is an array formula so comit with Ctrl-Shift-Enter

Last value,

=INDEX(Sheet3!$A$1:$E$1,MAX(IF(Sheet3!$A$2:$E$2>0,COLUMN(Sheet3!$A$1:$E$1)-COLUMN(Sheet3!$A$1)+1)))

Also an array.

HTH

Steve

 

[Guest]

Bob's formula should be:

=INDEX(1:1,1,MIN(IF(2:2<>0,COLUMN(1:1))))

Replace MIN with MAX to get last entry

HTH

 

[Ardus Petus]

Both formulas are array formulas (to be validated with Crl-Shift-Enter)

Min:
=INDEX(Feuil1!$A1:$E1,,MIN(IF(Feuil1!$A2:$E2>0,COLUMN(Feuil1!$A2:$E2),99999)
))

Max:
=INDEX(Feuil1!$A1:$E1,,MAX(IF(Feuil1!$A2:$E2>0,COLUMN(Feuil1!$A2:$E2),0)))

See example: http://cjoint.com/?eltiyaAmhZ

HTH

 

[Ardus Petus]

Bob's and Peo's and Topper's solutions make me feel like a rookie (which I
probably am)

 

[Guest]

Sucess, Thanks for everyone's help!