Formula to find values in row and return headings

[JV0710]

Good Day
Please can I get some help with a formula to find the values in a row and return the matching headings

In the table below I would like to list the Groups that have values in each row (In the column named "mix")
Example: in Cell A2, match the value and return "Grp 4", In cell A3, match the values and return "Grp1, Grp6, Grp7, Grp9" etc

Thanks in Advance

 

[awoohaw]

try this:

Mr Excel Questions 3.xlsm
	A	B	C	D	E	F	G	H	I	J
1	MIX	Grp1	Grp2	Grp3	Grp4	Grp5	Grp6	Grp7	Grp8
2	Grp1,Grp3,Grp4,Grp8	500		500	200				300
3	Grp2,Grp3,Grp5,Grp8		300	300		500			500
4	Grp2,Grp6,Grp8		200				400		200
5	Grp1,Grp2,Grp5,Grp6,Grp7,Grp8	500	300			500	200	200	300
6
Sheet5

Cell Formulas
Range	Formula
A2:A5	A2	=TEXTJOIN(",",TRUE,FILTER($B$1:$I$1,B2:I2>0,""))

 

[JV0710]

try this:

Mr Excel Questions 3.xlsm
	A	B	C	D	E	F	G	H	I	J
1	MIX	Grp1	Grp2	Grp3	Grp4	Grp5	Grp6	Grp7	Grp8
2	Grp1,Grp3,Grp4,Grp8	500		500	200				300
3	Grp2,Grp3,Grp5,Grp8		300	300		500			500
4	Grp2,Grp6,Grp8		200				400		200
5	Grp1,Grp2,Grp5,Grp6,Grp7,Grp8	500	300			500	200	200	300
6
Sheet5

Cell Formulas
Range	Formula
A2:A5	A2	=TEXTJOIN(",",TRUE,FILTER($B$1:$I$1,B2:I2>0,""))

Thanks very very for your reply. This does exactly what I need. Problem solved.

Just wondering . . . What would the formula be If I were to share this workbook with others who do not have Office 365. Not critical, but would be good learning for me

Thanks once again

 

[awoohaw]

Thanks very very for your reply. This does exactly what I need. Problem solved.

Just wondering . . . What would the formula be If I were to share this workbook with others who do not have Office 365. Not critical, but would be good learning for me

Thanks once again

Thanks for posting as an answer!. It would be a tad more difficult and I'm not that good with it.
You may want to it as a question again (since this is answered). But, I'm unsure if it is against the rules of posting the same question twice, but, you could preface it with "So I can share with non 365 users".

 

[shinigamilight]

Thanks very very for your reply. This does exactly what I need. Problem solved.

Just wondering . . . What would the formula be If I were to share this workbook with others who do not have Office 365. Not critical, but would be good learning for me

Thanks once again

Only possible with a user defined function as far as I know unless you have CONCAT function and tinker with it, but I have heard they removed it from excel 2016 using an update.

Excel Functions Missing in Office 2016: IFS, MAXIFS, MINIFS, SWITCH, CONCAT, TEXTJOIN - Office PowerUps

Can't find the IFS function in Excel 2016? Here's how to get it back. Also here are some other alternatives to the IFS function.

officepowerups.com

 

[JV0710]

Only possible with a user defined function as far as I know unless you have CONCAT function and tinker with it, but I have heard they removed it from excel 2016 using an update.

Excel Functions Missing in Office 2016: IFS, MAXIFS, MINIFS, SWITCH, CONCAT, TEXTJOIN - Office PowerUps

Can't find the IFS function in Excel 2016? Here's how to get it back. Also here are some other alternatives to the IFS function.

officepowerups.com

Thanks very much - Will continue to experiment and see

 

[Fluff]

unless you have CONCAT function and tinker with it, but I have heard they removed it from excel 2016 using an update.

Concat was never in 2016 only 2019 & newer.

If I were to share this workbook with others who do not have Office 365

What version are they using?

 

[awoohaw]

@Fluff and @shinigamilight ... thank you for continuing the conversation. I am at my knowledge limit with this one.

 

[shinigamilight]

I use excel 2016 and I have access to averageifs , but on microsoft's website it says not available on excel 2016. Microsoft is lying, they removed the functions with the update. The article attached above also says the same thing.

 

[Fluff]

Microsoft has NOT removed functions such as Ifs, maxifs, minifs, concat etc from 2016 as they were never released on 2016.

I have access to averageifs , but on microsoft's website it says not available on excel 2016.

Wrong it clearly shows that it is available on 2016

Although I do not trust that site as it is often wrong.