Lookup row value(s) and return column header

Hello all. Searched all day and tried many different formulas but to no avail.

Have it down to an INDEX and SUMPRODUCT function but can't quite figure it out.

Description:

Column headers are all dates, row headers are all customer names. Matrix data consists of 1,2,3,4 or 5.

I need a formula to lookup whether a 1, 2, 3, 4, or 5 appears, and result in the column header. There can be multiple values in one row so that is a complicating factor.

A1	A2	A3	A4	A5	A6	A7
Description	Jul 2016	Aug 2016	Sep 2016	Oct 2016	Nov 2016	RESULT
Customer 1	1	0	0	0	2	Jul 2016, Nov 2016
Customer 2	0	3	0	2	0	Aug 2016, Oct 2016
Customer 3	0	0	1	0	0	Sep 2016
Customer 4	0	0	3	1	0	Sep 2016, Nov 2016
Customer 5	1	0	0	0	0	Jul 2016
Customer 6	0	1	0	0	0	Aug 2016

Can someone point me in the right direction? Thank you!

as shown your table in A1 to F7 ( include header)

put your criteria on B9, ie 1 or 2 or 0

put this formula on C9

=IFERROR(INDEX($B$1:$F$1,MOD(SMALL(IF($B$2:$F$7=$B$9,(ROW($B$2:$F$7)-ROW($B$2)+1)*10^5+COLUMN($B$2:$F$7)-COLUMN($B$2)+1),ROWS($A$1:A1)),10^5)),"")

and press F2 button to open the formula, then press all together CTRL+SHIFT+ENTER button (array formula style), last step is copied down until blank results

hope it works

Originally Posted by humdingaling

see attached solution CSE Solution...confirmed by pressing CTRL+SHIFT+ENTER to activate the array, not just ENTER. You will know the array is active when you see curly braces { } appear around your formula. If you do not CTRL+SHIFT+ENTER you will get an error or a clearly incorrect answer.

Formula:

Please Login or Register  to view this content.

Wow, you're brilliant. Thank you. This worked phenomenally.

Or try this ...

=SUBSTITUTE(TRIM(IF(B2>0,TEXT($B$1,"MMM-YY "),"")&IF(C2>0,TEXT($C$1,"MMM-YY "),"")&IF(D2>0,TEXT($D$1,"MMM-YY "),"")&IF(E2>0,TEXT($E$1,"MMM-YY "),"")&IF(F2>0,TEXT($F$1,"MMM-YY "),""))," ",", ")