Index multiple column headers with multiple criteria

[lambertlambertlambert]

Hi,

I am trying to index the column headers (B1:E1) when they match colour (I3:I8), and have a value greater than 0. I would like to match all valid results and not just the first too please. An example of the results I am after are in J3:J8.

Any help greatly appreciated!

Book1
	A	B	C	D	E	F	G	H	I	J
1		Pen	Pencil	Crayon	Highlighter
2	Red	0	8	0	0
3	Green	5	0	5	0				Red	Pencil
4	Blue	1	0	0	0				Green	Pen, Crayon
5	Orange	5	0	0	2				Blue	Pen
6	Purple	1	2	0	0				Orange	Pen, Highlighter
7	Yellow	0	0	0	1				Purple	Pen, Pencil
8									Yellow	Highlighter
Sheet2

 

[jasonb75]

What kind of solution are you able to use?

This can only be done with a formula if you have excel 2019 or newer. Anything older will either require vba or a very long formula to reference each column, one at a time.
This is done in office 365 using your example but will also work in 2019 if the formula is array confirmed.

Book1
	I	J
3	Red	Pencil
4	Green	Pen, Crayon
5	Blue	Pen
6	Orange	Pen, Highlighter
7	Purple	Pen, Pencil
8	Yellow	Highlighter
Sheet1

Cell Formulas
Range	Formula
J3:J8	J3	=TEXTJOIN(", ",1,IF(INDEX($B$2:$E$7,MATCH(I3,$A$2:$A$7,0),0)>0,$B$1:$E$1,""))

 

[lambertlambertlambert]

What kind of solution are you able to use?

This can only be done with a formula if you have excel 2019 or newer. Anything older will either require vba or a very long formula to reference each column, one at a time.
This is done in office 365 using your example but will also work in 2019 if the formula is array confirmed.

Book1
	I	J
3	Red	Pencil
4	Green	Pen, Crayon
5	Blue	Pen
6	Orange	Pen, Highlighter
7	Purple	Pen, Pencil
8	Yellow	Highlighter
Sheet1

Cell Formulas
Range	Formula
J3:J8	J3	=TEXTJOIN(", ",1,IF(INDEX($B$2:$E$7,MATCH(I3,$A$2:$A$7,0),0)>0,$B$1:$E$1,""))

Unfortunately I am using excel 2013. VBA is an option if formula not possible for my version! Thanks very much for your reply

 

[jasonb75]

This is done as a vba function, enter the code into a standard vba module

Function aconcat(a As Variant, Optional sep As String = "") As String
    ' Harlan Grove, Mar 2002
    ' With a minor edit to ignore blanks
Dim y As Variant
If TypeOf a Is Range Then
    For Each y In a.Cells
        If Len(y.Value) > 0 Then aconcat = aconcat & y.Value & sep
    Next y
ElseIf IsArray(a) Then
    For Each y In a
        If Len(y) > 0 Then aconcat = aconcat & y & sep
    Next y
Else
    aconcat = aconcat & a & sep
End If
    If Len(aconcat) > 0 Then aconcat = Left(aconcat, Len(aconcat) - Len(sep)) Else aconcat = ""
End Function

Then use this formula in your sheet, which must be array confirmed with Ctrl Shift Enter. I think that this is correct but have not had time to test it.

=ACONCAT(IFERROR(IF(INDEX($B$2:$E$7,MATCH(I3,$A$2:$A$7,0),0)>0,$B$1:$E$1,""),""),", ")