Index multiple column headers with multiple criteria

Joined
Sep 23, 2021
Messages
2
Office Version
  1. 2013
Platform
  1. Windows
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
ABCDEFGHIJ
1PenPencilCrayonHighlighter
2Red0800
3Green5050RedPencil
4Blue1000GreenPen, Crayon
5Orange5002BluePen
6Purple1200OrangePen, Highlighter
7Yellow0001PurplePen, Pencil
8YellowHighlighter
Sheet2
 

Excel Facts

Last used cell?
Press Ctrl+End to move to what Excel thinks is the last used cell.
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
IJ
3RedPencil
4GreenPen, Crayon
5BluePen
6OrangePen, Highlighter
7PurplePen, Pencil
8YellowHighlighter
Sheet1
Cell Formulas
RangeFormula
J3:J8J3=TEXTJOIN(", ",1,IF(INDEX($B$2:$E$7,MATCH(I3,$A$2:$A$7,0),0)>0,$B$1:$E$1,""))
 
Upvote 0
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
IJ
3RedPencil
4GreenPen, Crayon
5BluePen
6OrangePen, Highlighter
7PurplePen, Pencil
8YellowHighlighter
Sheet1
Cell Formulas
RangeFormula
J3:J8J3=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
 
Upvote 0
This is done as a vba function, enter the code into a standard vba module
VBA Code:
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.
Excel Formula:
=ACONCAT(IFERROR(IF(INDEX($B$2:$E$7,MATCH(I3,$A$2:$A$7,0),0)>0,$B$1:$E$1,""),""),", ")
 
Upvote 0

Forum statistics

Threads
1,223,885
Messages
6,175,184
Members
452,615
Latest member
bogeys2birdies

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top