-1

Following is the table in Excel 

Sr. No  English Hindi   Physics Maths
1       1       0       1       0
2       0       1       0       1
3       1       1       1       1
4       0       1       0       1
5       0       0       0       1
6       1       1       1       0
7       0       0       1       1
8       1       0       0       0
9       0       0       1       1
10      1       1       1       1

I want to display all Sr. No where Subject=1. For example if Subject=Physics then display Sr No. 1,3,6,7,9,10

Scott Craner
  • 148,073
  • 10
  • 49
  • 81
Sushil
  • 1

1 Answers1

1

If you have Office 365 Excel then use TEXTJOIN as an array formula:

=TEXTJOIN(",",TRUE,IF(INDEX($A$1:$E$11,0,MATCH($H$2,$A$1:$E$1,0))=1,$A$1:$A$11,""))

This finds the correct column and then iterates finding the 1 and returns the number in column A where 1 was found.

enter image description here

If you do not have Office 365 then look at my answer on THIS POST for a vba function that mimics TEXTJOIN.

Put that code in a module and use the formula as described above.

Scott Craner
  • 148,073
  • 10
  • 49
  • 81