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

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

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

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