Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#8
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Finding last value greater than 0 | Excel Worksheet Functions | |||
How can I get Excel to find the first number in a list greater tha | Excel Worksheet Functions | |||
Edit + Find | Excel Discussion (Misc queries) | |||
find a cells from a range of cell | Excel Worksheet Functions | |||
Excel has a "Find Next" command but no "Find Previous" command. | Excel Discussion (Misc queries) |