Reverse lookup

[Guest]

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 value>0 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

 

[Biff]

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(A22),INDEX(A$1$1,MATCH(MAX(A22),A22,0)),"")

Copy down as needed.

For rows with all zeros the result will be a blank cell.

Biff

 

[Guest]

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

 

[Biff]

You're welcome. Thanks for the feedback!

Biff

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