Index matching and concatenating more than one result.

[Kamran01]

Dear Experts,

Hope you all will be good.

I want failing subjects result in one cell. Failing criteria is less than 34 marks subject.

Student MATH SCIENCE PHYSICS CHEMISTRY TOTAL RESULT FAILED SUBJECTS
A 50 30 10 70 160 FAILED SCIENCE, PHYSICS
B 30 50 45 44 169 FAILED MATH

Thank advance for your prompt response,

Best Regard,
Kamran Noor

 

[Ramballah]

Dear Experts,

Hope you all will be good.

I want failing subjects result in one cell. Failing criteria is less than 34 marks subject.

Student MATH SCIENCE PHYSICS CHEMISTRY TOTAL RESULT FAILED SUBJECTS
A 50 30 10 70 160 FAILED SCIENCE, PHYSICS
B 30 50 45 44 169 FAILED MATH

Thank advance for your prompt response,

Best Regard,
Kamran Noor

Can you upload your table again but in correct formatting? and so basically u want in the column of subjects to show all the subjects student has failed in one cell? So for Student A u cant science and physics in 1 cell under the column subjects?

 

[Kamran01]

Actually i am unaware, how to post in cells form on this page.

Student | MATH | SCIENCE | PHYSICS | CHEMISTRY | TOTAL | RESULT | FAILED SUBJECTS
A | 50 | 30 | 10 | 70 | 160 | FAILED | SCIENCE, PHYSICS
B | 30 | 50 | 45 | 44 | 169 | FAILED | MATH

Can you please advise how to post in table form here.

 

[Ramballah]

[TABLE="width: 576"]
<colgroup><col width="64" span="9" style="width:48pt"> </colgroup><tbody>[TR]
[TD="class: xl63, width: 64"]Student[/TD]
[TD="class: xl63, width: 64"]MATH[/TD]
[TD="class: xl63, width: 64"]SCIENCE[/TD]
[TD="class: xl63, width: 64"]PHYSICS[/TD]
[TD="class: xl63, width: 64"]CHEMISTRY[/TD]
[TD="class: xl63, width: 64"]TOTAL[/TD]
[TD="class: xl63, width: 64"]RESULT[/TD]
[TD="class: xl63, width: 64"]FAILED[/TD]
[TD="class: xl63, width: 64"]SUBJECTS[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]10[/TD]
[TD="align: right"]70[/TD]
[TD="align: right"]160[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]SCIENCE, PHYSICS[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD="align: right"]30[/TD]
[TD="align: right"]50[/TD]
[TD="align: right"]45[/TD]
[TD="align: right"]44[/TD]
[TD="align: right"]169[/TD]
[TD][/TD]
[TD][/TD]
[TD="class: xl64"]MATH[/TD]
[/TR]
</tbody>[/TABLE]

I just select my range in excel and copy and paste here

 

[bhos123]

=if(b2<35,b$1&",","")&if(c2<35,c$1&",","")&if(d2<35,d$1&",","")&if(e2<35,e$1&",","")

 

[Kamran01]

Done

 

[AhoyNC]

If you have Excel Office 365 you could use the TEXTJOIN function.
This is an array formula and must be entered with CTRL-SHIFT-ENTER. Drag formla down as needed.

Excel Workbook
	A	B	C	D	E	F	G	H	I
1	Student	MATH	SCIENCE	PHYSICS	CHEMISTRY	TOTAL	RESULT	FAILED	SUBJECTS
2	A	50	30	10	70	160			SCIENCE, PHYSICS
3	B	30	50	45	44	169			MATH
Sheet

 

[Kamran01]

Thank you AhoyNC but i don't have textjoin function in 2016 office

 

[jtakw]

Hi,

Try this if you don't want any leading and/or trailing commas in the result:

Book1
	A	B	C	D	E	F	G	H
1	Student	MATH	SCIENCE	PHYSICS	CHEMISTRY	TOTAL	RESULT	FAILED SUBJECTS
2	A	50	30	10	70	160		SCIENCE, PHYSICS
3	B	30	50	45	44	169		MATH
Sheet486

Cell Formulas
Range	Formula
H2	=MID(IF(B2<34,", "&B$1,"")&IF(C2<34,", "&C$1,"")&IF(D2<34,", "&D$1,"")&IF(E2<34,", "&E$1,""),3,99)