Copy Column Header if cell in column meets criteria

[flds]

Hello,

I am trying to figure out a formula that gives me the header result.

example:
H2 the result should be DEF
H3 the result should be FGH
H4 the result should be DEF
H5 the result should be BCD
H6 the result should be ABC
H7 the result should be ABC

Excel Spreadsheet
A B C D E F G H
1 ABC BCD CDE DEF EFG FGH
2 -1 -1 -5 9 -1 -1 =IF(B3:G3>0,B2:G2,"")
3 -1 -1 -5 -1 -1 9
4 -1 -1 -1 5 -1 -1
5 -2 8 -6 4 -2 -2
6 6 -1 -1 -1 -1 -2
7 4 -3 -7 3 -3 6

I hope someone could help me in this formula.

Thanks
FLDS

 

[T. Valko]

Hi Valko and Branco
Thank you for the formulas. Both the formulas seem to work as requested.

I need an amendment to the formulas. Did not realize this earlier. Sorry for that.

The data will be alternated on each row (A2…F2, A4… F4, A6…F6 so on)
And the results transposed in cells H2, I2, J2, K2

Is there a formula for this change and to copy across.

A B C D E F G H I J K L M
1 ABC BCD CDE DEF EFG FGH Result 2 -1 -1 -5 9 -1 -1 DEF FGH DEF BCD ABC ABC
3
4 -1 -1 -5 -1 -1 9
5
6 -1 -1 -1 5 -1 -1
7
8 -2 8 -6 -4 -2 -2
9
10 6 -1 -1 -1 -1 -2
11
12 4 -3 -7 -3 -3 -6

Thanks for your time and help

FLDS

Try this...

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M
1	ABC	BCD	CDE	DEF	EFG	FGH	_	_	_	_	_	_	_
2	-1	-1	-5	9	-1	-1	_	DEF	FGH	DEF	BCD	ABC	ABC
3	_	_	_	_	_	_	_	_	_	_	_	_	_
4	-1	-1	-5	-1	-1	9	_	_	_	_	_	_	_
5	_	_	_	_	_	_	_	_	_	_	_	_	_
6	-1	-1	-1	5	-1	-1	_	_	_	_	_	_	_
7	_	_	_	_	_	_	_	_	_	_	_	_	_
8	-2	8	-6	-4	-2	-2	_	_	_	_	_	_	_
9	_	_	_	_	_	_	_	_	_	_	_	_	_
10	6	-1	-1	-1	-1	-2	_	_	_	_	_	_	_
11	_	_	_	_	_	_	_	_	_	_	_	_	_
12	4	-3	-7	-3	-3	-6	_	_	_	_	_	_	_
Sheet1

This array formula** entered in H2 and copied across:

=INDEX($A1:$F1,MATCH(TRUE,INDEX($A1:$F12,COLUMNS($H2:H2)*2,0)>0,0))

** array formulas need to be entered using the key
combination of CTRL,SHIFT,ENTER (not just ENTER).
Hold down both the CTRL key and the SHIFT key
then hit ENTER.

 

[markmzz]

A small modification:

=OFFSET($A1,,MIN(IF(OFFSET($A$2:$F$2,2*(COLUMNS($H$1:H1)-1),)>0,COLUMN($A$2:$F$2)-COLUMN($A$2)+1))-1)

Markmzz

 

[flds]

Mark,

Thank you so much for the formula.
This worked out the way I wanted.

FLDS

 

[markmzz]

Mark,

Thank you so much for the formula.
This worked out the way I wanted.

FLDS

You are Welcome.

And thank you for the feedback.

Markmzz