find first value in a row with value greater than 0
 

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

find first value in a row with value greater than 0
 

=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)

"C-A" wrote in message
...
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

find first value in a row with value greater than 0
 

=MATCH(TRUE,A2:E20,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:E20,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


"C-A" wrote in message
...
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

find first value in a row with value greater than 0
 

First Value

=INDEX(Sheet3!$A$1:$E$1,MIN(IF(Sheet3!$A$2:$E$20, 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$20, COLUMN(Sheet3!$A$1:$E$1)-COLUMN(Sheet3!$A$1)+1)))

Also an array.

HTH

Steve


--
SteveG
------------------------------------------------------------------------
SteveG's Profile: http://www.excelforum.com/member.php...fo&userid=7571
View this thread: http://www.excelforum.com/showthread...hreadid=531958

find first value in a row with value greater than 0
 

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

"Peo Sjoblom" wrote:

=MATCH(TRUE,A2:E20,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:E20,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


"C-A" wrote in message
...
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

find first value in a row with value greater than 0
 

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

Min:
=INDEX(Feuil1!$A1:$E1,,MIN(IF(Feuil1!$A2:$E20,COL UMN(Feuil1!$A2:$E2),99999)
))

Max:
=INDEX(Feuil1!$A1:$E1,,MAX(IF(Feuil1!$A2:$E20,COL UMN(Feuil1!$A2:$E2),0)))

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

HTH
--
AP

"C-A" a écrit dans le message de
...
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

find first value in a row with value greater than 0
 

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

--
AP

"Ardus Petus" a écrit dans le message de
...
Both formulas are array formulas (to be validated with Crl-Shift-Enter)

Min:

=INDEX(Feuil1!$A1:$E1,,MIN(IF(Feuil1!$A2:$E20,COL UMN(Feuil1!$A2:$E2),99999)
))

Max:
=INDEX(Feuil1!$A1:$E1,,MAX(IF(Feuil1!$A2:$E20,COL UMN(Feuil1!$A2:$E2),0)))

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

HTH
--
AP

"C-A" a écrit dans le message de
...
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

find first value in a row with value greater than 0
 

Sucess, Thanks for everyone's help!

"C-A" wrote:

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