Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse lookup
I need a better formula that will return a column header value if a cell in a
row contains a value greater than 0. I have over 1000 rows of data. Every row where value0 needs to reference the text value in the corresponding column header. I know I could replicate the same table using an if statement for each cell formula, but I would like to return one column of values instead of using multiple columns. Not sure if I am on the right track? {=if($A2:$d2)0,then return value from range ($A$1:$D$1), "")} C1 C2 C3 C4 Formula Result C5 R1 H P E J R2 0 0 0 4 J R3 8 0 0 0 H R4 0 8 0 0 P Need help! Rick |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse lookup
Hi!
Will there only be 1 non-zero value in each row? Will there be any rows will all zeros? This formula assumes there will be rows with all zeros and that there will only be 1 non-zero value per row: =IF(SUM(A2:D2),INDEX(A$1:D$1,MATCH(MAX(A2:D2),A2:D 2,0)),"") Copy down as needed. For rows with all zeros the result will be a blank cell. Biff "Rick" wrote in message ... I need a better formula that will return a column header value if a cell in a row contains a value greater than 0. I have over 1000 rows of data. Every row where value0 needs to reference the text value in the corresponding column header. I know I could replicate the same table using an if statement for each cell formula, but I would like to return one column of values instead of using multiple columns. Not sure if I am on the right track? {=if($A2:$d2)0,then return value from range ($A$1:$D$1), "")} C1 C2 C3 C4 Formula Result C5 R1 H P E J R2 0 0 0 4 J R3 8 0 0 0 H R4 0 8 0 0 P Need help! Rick |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse lookup
Biff,
Hello. Works great! Yes, there will always be one value in each row or all zeros. In looking through my data set everything seems to work fine. Much appreciated! Rick -- Need help! Rick "Biff" wrote: Hi! Will there only be 1 non-zero value in each row? Will there be any rows will all zeros? This formula assumes there will be rows with all zeros and that there will only be 1 non-zero value per row: =IF(SUM(A2:D2),INDEX(A$1:D$1,MATCH(MAX(A2:D2),A2:D 2,0)),"") Copy down as needed. For rows with all zeros the result will be a blank cell. Biff "Rick" wrote in message ... I need a better formula that will return a column header value if a cell in a row contains a value greater than 0. I have over 1000 rows of data. Every row where value0 needs to reference the text value in the corresponding column header. I know I could replicate the same table using an if statement for each cell formula, but I would like to return one column of values instead of using multiple columns. Not sure if I am on the right track? {=if($A2:$d2)0,then return value from range ($A$1:$D$1), "")} C1 C2 C3 C4 Formula Result C5 R1 H P E J R2 0 0 0 4 J R3 8 0 0 0 H R4 0 8 0 0 P Need help! Rick |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Reverse lookup
You're welcome. Thanks for the feedback!
Biff "Rick" wrote in message ... Biff, Hello. Works great! Yes, there will always be one value in each row or all zeros. In looking through my data set everything seems to work fine. Much appreciated! Rick -- Need help! Rick "Biff" wrote: Hi! Will there only be 1 non-zero value in each row? Will there be any rows will all zeros? This formula assumes there will be rows with all zeros and that there will only be 1 non-zero value per row: =IF(SUM(A2:D2),INDEX(A$1:D$1,MATCH(MAX(A2:D2),A2:D 2,0)),"") Copy down as needed. For rows with all zeros the result will be a blank cell. Biff "Rick" wrote in message ... I need a better formula that will return a column header value if a cell in a row contains a value greater than 0. I have over 1000 rows of data. Every row where value0 needs to reference the text value in the corresponding column header. I know I could replicate the same table using an if statement for each cell formula, but I would like to return one column of values instead of using multiple columns. Not sure if I am on the right track? {=if($A2:$d2)0,then return value from range ($A$1:$D$1), "")} C1 C2 C3 C4 Formula Result C5 R1 H P E J R2 0 0 0 4 J R3 8 0 0 0 H R4 0 8 0 0 P Need help! Rick |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
reverse mapping for LOOKUP table? | Excel Worksheet Functions | |||
Need help with reverse phone lookup | Excel Worksheet Functions | |||
Lookup Vector > Lookup Value | Excel Worksheet Functions | |||
Lookup function w/Text and Year | Excel Worksheet Functions | |||
double lookup, nest, or macro? | Excel Worksheet Functions |