Excel Index Match Return Muliple Values

kashifshahzad

New Member
Joined
May 17, 2014
Messages
17
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
 
Last edited:

Excel Facts

Select all contiguous cells
Pressing Ctrl+* (asterisk) will select the "current region" - all contiguous cells in all directions.
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:
Rich (BB code):
=ROW(Name)-ROW(INDEX(Name,1,1))+1

In A3 control+shift+enter, not just enter:
Rich (BB code):
=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:
Rich (BB code):
=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))),"")
 
Upvote 0
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.
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,513
Messages
6,114,072
Members
448,546
Latest member
KH Consulting

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