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,

		Total	Fuel	Travel	Mobile	Sundries
					0.8
43374
43374	Tesco's	40.01	33.34				Fuel
43374	Anxiety Transfer Experts	120
43374	O2	80			66.66666667		Mobile

<tbody>
</tbody>

 

[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!!