Index,small returning multiples of the same result

P

palaeontology

Active Member
Joined
May 12, 2017
Messages
444
Office Version
  1. 2016
Platform
  1. Windows
The following is a small excerpt of student numbers, student names, class codes and corresponding teacher names from a much larger list in columns A, B, C, D of a sheet called 'Entry'

Student NumberStudent NameClass CodeTeacher
85464ALLEN, Jake07MAT303SPERE
62092AXELSEN, Olivia07MAT303SPERE
84723BAZIN, Lille07MAT303SPERE
87213BULLER, Thomas07MAT303SPERE
56913CAMPBELL, Archie07MAT303SPERE
87649CHRISTOU, Emily07MAT303SPERE
85163CHURCHILL, Liv07MAT303SPERE
81515EVANS, Jayden07MAT303SPERE
55794FENTON, Zara07MAT303SPERE
67378FORRESTER, Christian07MAT303SPERE
84405GRAETZ, Tom07MAT303SPERE
67076HARVIE, Matthew07MAT303SPERE
57181KILLEN, Finn07MAT303SPERE
75487KING, James07MAT303SPERE
79921LIU, William07MAT303SPERE
62769MCKEE, Jack07MAT303SPERE
67950METZNER, Sachiko07MAT303SPERE
85910NEILSEN, Emma07MAT303SPERE
86420NOTT, Maya07MAT303SPERE
81547SNEATH, Bradley07MAT303SPERE
81699SODHI, Jujhar07MAT303SPERE
86286VAN BEEST, Kye07MAT303SPERE
79970WOODS, Annabel07MAT303SPERE

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>
77710ALLAN, Thomas07MAT404RTHOM
81557CATFORD, Olivia07MAT404RTHOM
82638DUNN, Cameron07MAT404RTHOM
70063FITZGERALD, Harry07MAT404RTHOM
60070FOLEY, Oscar07MAT404RTHOM
86424FOWLER, Matthew07MAT404RTHOM
61329HAMILTON, Bobby07MAT404RTHOM
72306HARRISON, Jessica07MAT404RTHOM
85281HARTDEGEN, Siena07MAT404RTHOM
64249HILE, Ronan07MAT404RTHOM
85108KENNER, Makenza07MAT404RTHOM
79361LEES, Sonya07MAT404RTHOM
77920LONGMIRE, Hugh07MAT404RTHOM
74840MARTIN, Sophie07MAT404RTHOM
77186MCLEOD, Max07MAT404RTHOM
71436MESSER, Bridget07MAT404RTHOM
78262NGUYEN-TRAN, Aiden07MAT404RTHOM
80654OSTINI, Tobias07MAT404RTHOM
84830OTAKE, Haru07MAT404RTHOM
86109PEDLEY, Charlotte07MAT404RTHOM
58041POON, Keenan07MAT404RTHOM
64520RICHARDSON, Yenn07MAT404RTHOM
83246SCHMIDT, Gabrielle07MAT404RTHOM
68713STONE, Nicola07MAT404RTHOM
87642BAEK, Bryan07MAT501GBYGR
81555BANGA, Pragun07MAT501GBYGR
84747BELL, Dominic07MAT501GBYGR
77108BRENNAN, Poppy07MAT501GBYGR
78158DUVAL, Brianna07MAT501GBYGR
84980ENARES-WORMALD, Grace07MAT501GBYGR
78096FINLAYSON, Joe07MAT501GBYGR
58463HALL, Matthew07MAT501GBYGR
74333IVERMEE, Joe07MAT501GBYGR
83550JOYCE, Imogen07MAT501GBYGR
81512KETTLE, Zoe07MAT501GBYGR
83879KING, Luka07MAT501GBYGR
72297KRUGER, Sophie07MAT501GBYGR
75206MADDOCK, Isabel07MAT501GBYGR
87653MARSDEN, Kate07MAT501GBYGR
57653MATHER, Chloe07MAT501GBYGR
85595MONCUR, Marcus07MAT501GBYGR
81757RAMSAY, Lily07MAT501GBYGR
57006STAPLES, Ed07MAT501GBYGR
84343TALJAARD, Chloe07MAT501GBYGR
77816VOORHOEVE, Jules07MAT501GBYGR
74802WHITEHEAD, Josh07MAT501GBYGR

<colgroup><col><col><col><col></colgroup><tbody>
</tbody>

On another sheet, called 'IndivStudentSelect', in cell V12, I'm entering a class code of my choosing, eg: I might choose to look at the class called 07MAT404

Starting two cells lower than that, in cell V14, I'm entering the following array formula in a hope to make a list of all the students in that class ...

{=IF(V$12="","",IFERROR(INDEX(Entry!$B$1:$B$309,SMALL(IF(Entry!$C$1:$C$309=V$12,ROW(Entry!$C$1:$C$309)-ROW(Entry!$C$1)+1),ROWS(Entry!$C$1:$C1))),""))}

obviously, I'm not typing in the brace brackets into the formula on the sheet. I placed them here, so you'd know I was definitely using an array formula.

What I should be getting is the following list ...

ALLAN, Thomas
CATFORD, Olivia
DUNN, Cameron
FITZGERALD, Harry
FOLEY, Oscar
FOWLER, Matthew
HAMILTON, Bobby
HARRISON, Jessica
HARTDEGEN, Siena
HILE, Ronan
KENNER, Makenza
LEES, Sonya
LONGMIRE, Hugh
MARTIN, Sophie
MCLEOD, Max
MESSER, Bridget
NGUYEN-TRAN, Aiden
OSTINI, Tobias
OTAKE, Haru
PEDLEY, Charlotte
POON, Keenan
RICHARDSON, Yenn
SCHMIDT, Gabrielle
STONE, Nicola



But what I seem to be getting is the following ....




ALLAN, Thomas
CATFORD, Olivia
DUNN, Cameron
DUNN, Cameron
FITZGERALD, Harry
FITZGERALD, Harry
MESSER, Bridget
NGUYEN-TRAN, Aiden
OSTINI, Tobias
OTAKE, Haru
PEDLEY, Charlotte
POON, Keenan
RICHARDSON, Yenn
SCHMIDT, Gabrielle
STONE, Nicola


<colgroup><col></colgroup><tbody>
</tbody>

<colgroup><col></colgroup><tbody>
</tbody>
Clearly, some names are being repeated, and some are being missed.

I try different class codes, and every time, it always starts to give me repeated names from about the 3rd name into the list, and from there down, it either repeats names, or skips names.

Can anyone see what is wrong with my array formula ?
 

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.
Sort by date Sort by votes
In V12 of IndivStudentSelect enter:

=COUNTIFS(Entry!C2:C70,V12)

In V14 of IndivStudentSelect control+shift+enter, not just enter, and copy down:

=IF(ROWS($V$14:V14)>$V$13,"",INDEX(Entry!$B$2:$B$70,SMALL(IF(Entry!$C$2:$C$70=$V$12,ROW(Entry!$B$2:$B$70)-ROW(Entry!$B$2)+1),ROWS($V$14:V14))))
 
Upvote 0
You must log in or register to reply here.

Forum statistics

Threads
1,214,832
Messages
6,121,844
Members
449,051
Latest member
excelquestion515

Share this page

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