Show cell value based upon data contained in column below

[CarlT]

Hey all,

I'm trying to work out the best formula to use for the below requirements.

I have my sheet below, I'm looking to show the top field (row 1), if there is any amount within the corresponding columns below, into a new column. What formula am I best working with? This could extend to 20+ columns. Thinking HLookup or IFS..

Thanks in advance,


[TABLE="width: 512"]
<tbody>[TR]
[TD="width: 64"][/TD]
[TD="width: 64"][/TD]
[TD="width: 64"]Total[/TD]
[TD="width: 64"]Fuel[/TD]
[TD="width: 64"]Travel[/TD]
[TD="width: 64"]Mobile[/TD]
[TD="width: 64"]Sundries[/TD]
[TD="width: 64"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]0.8[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]43374[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]43374[/TD]
[TD]Tesco's[/TD]
[TD="align: right"]40.01[/TD]
[TD="align: right"]33.34[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD]Fuel[/TD]
[/TR]
[TR]
[TD="align: right"]43374[/TD]
[TD]Anxiety Transfer Experts[/TD]
[TD="align: right"]120[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD="align: right"]43374[/TD]
[TD]O2[/TD]
[TD="align: right"]80[/TD]
[TD][/TD]
[TD][/TD]
[TD="align: right"]66.66666667[/TD]
[TD][/TD]
[TD]Mobile[/TD]
[/TR]
</tbody>[/TABLE]

 

[Chrisdontm]

Assuming that row two is where that " 0.8 " is, then in cell H2 put this formula;

=IF(LARGE((D2:G2>0)*COLUMN(D2:G2),1)=0,"",INDEX($A$1:$G$1,LARGE((D2:G2>0)*COLUMN(D2:G2),1)))

This is an array formula, so to enter it, you will need to hold control & shift then hit enter...

Then copy it down as far as you need top.

( Widen the listed ranges as needed... )

 

[CarlT]

Awesome, thank you!!