Searching an array of cells for a value and returning the column headers where a value exists

Mattyastill

New Member
Joined
Nov 27, 2017
Messages
18
Hi,

I'm trying to find a way which i can return the column headers where a value exists in the cells F:M for the same row. As you can see from the screenshot for the first row, i would like to search for any value in row 3 and bring back the column heads for the rows. I have shown in Column N of what i would like to return if there is a value there.

I have tried a combination of Index, Match Index, Max and Index, Sum-product but just cant seem to get it working.

1576156578052.png



Any help would be much appreciated

Thanks,

Matthew
 

Some videos you may like

Excel Facts

Lock one reference in a formula
Need 1 part of a formula to always point to the same range? use $ signs: $V$2:$Z$99 will always point to V2:Z99, even after copying

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
What version of Xl are you using?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
In that case try
=TEXTJOIN(", ",1,IF(F3:M3<>"",F2:M2,""))

This will need array entry, Ctrl Shift Enter, unless you have the new Dynamic Array Functions
 

Mattyastill

New Member
Joined
Nov 27, 2017
Messages
18
In that case try
=TEXTJOIN(", ",1,IF(F3:M3<>"",F2:M2,""))

This will need array entry, Ctrl Shift Enter, unless you have the new Dynamic Array Functions
Ahhh yes that works perfectly thanks so much, only issue is in some of my columns further down in the data there are 0's rather than all being blank. Could there be additional criteria added to this so it ignores blanks and 0's?
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
How about
=TEXTJOIN(", ",1,IF((F3:M3<>"")*(F3:M3<>0),F2:M2,""))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
36,145
Office Version
365
Platform
Windows
You're welcome & thanks for the feedback
 

Watch MrExcel Video

Forum statistics

Threads
1,090,457
Messages
5,414,637
Members
403,539
Latest member
rthompsona

This Week's Hot Topics

Top