Excel Index Match Return Muliple Values

kashifshahzad

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

Some videos you may like

Excel Facts

What is the shortcut key for Format Selection?
Ctrl+1 (the number one) will open the Format dialog for whatever is selected.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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))),"")
 

kashifshahzad

New Member
Joined
May 17, 2014
Messages
15

ADVERTISEMENT

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

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,201
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

New Member
Joined
May 17, 2014
Messages
15

ADVERTISEMENT

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

kashifshahzad

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

Watch MrExcel Video

Forum statistics

Threads
1,123,176
Messages
5,600,140
Members
414,365
Latest member
UUR

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
Top