Excel Index Match Return Muliple Values

[kashifshahzad]

I have two sheet in a file on one sheet a have data regarding student like AdmissioNo, RollNo, Name, FatherName, Class and status. I want to generate the student list according to choice, for example if I select class 1 it should return student name list of that particular class if their status is active.
I have used following formula in my second sheet
=INDEX(Data!C2:C1019,MATCH(Sheet2!$B$1,Data!E2:E1019,0))
it gives me the result, but for few classes it gives repetition of student names.
please download file on link mentioned below

Need your quick help.
Thanks

https://www.dropbox.com/s/jle1078znmc8ln3/Assignment.xlsx?dl=0

 

[Aladin Akyurek]

The sheet where processing must occur...

A1 houses a Class of interest.

A2 houses a Status value like 'active.'

The ranges which are defined using the Name Box are AdmissioNo, RollNo, Name, Father, Class, and Status. Each named range runs from row 2 to row 1019.

Define also Ivec using Formulas | Name Manager as referring to:

=ROW(Name)-ROW(INDEX(Name,1,1))+1

In A3 control+shift+enter, not just enter:

=SUM(IF(FREQUENCY(IF(1-(Name=""),IF(Class=$A$1,
    IF(Status=$A$2,MATCH(Name,Name,0)))),Ivec),1))

A4: LIST

In A5 control+shift+enter, not just enter, and copy down:

=IF(ROWS($A$5:A5)<=$A$3,INDEX(Name,SMALL(IF(FREQUENCY(IF(1-(Name=""),
    IF(Class=$A$1,IF(Status=$A$2,MATCH(Name,Name,0)))),Ivec),Ivec),
    ROWS($A$5:A5))),"")

 

[kashifshahzad]

Thanks you very much for your quick reply. I tried but couldn't success. can you please implement this in this file.
https://www.dropbox.com/s/jle1078znm...ment.xlsx?dl=0


Thanks again...

 

[Aladin Akyurek]

Thanks you very much for your quick reply. I tried but couldn't success. can you please implement this in this file.
https://www.dropbox.com/s/jle1078znm...ment.xlsx?dl=0


Thanks again...

You should do this yourself, paying attention to the details of the processing sheet.

 

[kashifshahzad]

ok. Can you explain what is ivec? how can I create ivec? thanks.

 

[Aladin Akyurek]

ok. Can you explain what is ivec? how can I create ivec? thanks.

Activate Formulas | Name Manager.
Activate the New tab.
Type Ivec in the Name box.
Type in the Refers to box:

=ROW(Name)-ROW(INDEX(Name,1,1))+1

Click OK.

You are done, assuming that you created other definitions using the Name Box on the Formula Bar.

 

[kashifshahzad]

Great....its worked... Thank you very much. God bless you....

 

[Aladin Akyurek]

Great....its worked... Thank you very much. God bless you....

You are welcome.

 

[kashifshahzad]

I have file including following worksheets
1. Settings
2. Attendance Register (It contains data of students name, class,status, date)
3. Class Attendance Report (user has choice tochoose any class from list, and the relevant data of that particular class comedown in sheet like rollno, name, month and attendance percentage
Problem. In the sheet named ClassAttendance Report when user choose class name from the list the rollno, studentname data is coming right, but the attendance percentage which I calculatedgives wrong value, I want the exact attendance value of particular rollno whichuser choose from the list.
See the uploaded file forreference from below link.
https://www.dropbox.com/s/j5ljsdvfb2ltp47/attt%20Ver%202.xlsx?dl=0<strike></strike>

Thanks in advance