array formula returning 'circular reference' error

palaeontology

Active Member
Joined
May 12, 2017
Messages
383
Office Version
  1. 2016
Platform
  1. Windows
Hello to all,

I have the following sheet, called ... Full School List ...
Full School List.JPG


I have another sheet called ... Bradman ...
Bradman array formula.JPG


In BradmanB10 I have the following array formula ...

{=IFERROR(INDEX('Full School List'!$K$2:$K$2500, MATCH(0, COUNTIF(B$10:B10, 'Full School List'!$K$2:$K$2500)+IF('Full School List'!$F$2:$F$2500<>B$5, 1, 0)+IF('Full School List'!$S$2:$S$2500<>B$6, 1, 0), 0)),"")}

but as you can see in the image it's returning zeros, with the error message ...

error message.JPG


The formula is meant to return the surnames of students (from column K in the 'Full School List' worksheet) who meet two conditions ...
1. they are in yr 7
2. they are female

When I evaluate the formula, i get down to the following final step ...

evaluate formula.JPG


and it shows that the surname Adams is the first name that should appear in the list (as it has met both criteria), and this is correct, but it then goes and returns a zero.

Can anyone see where I've gone wrong ?

Kind regards,

Chris
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
I will have a look at at, but only later today.
 

Excel Facts

Format cells as time
Select range and press Ctrl+Shift+2 to format cells as time. (Shift 2 is the @ sign).

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
Can you post some copyable data using the XL2BB add-in, that way members don't have to try & recreate your data.
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Can you post some copyable data using the XL2BB add-in, that way members don't have to try & recreate your data.
But please do not include real names.
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
383
Office Version
  1. 2016
Platform
  1. Windows
ok, 230 made up names (thankyou 'random names' google search) ....

Book1
ABCDEFGHIJKLM
1IDStudent NameStatusSynergyMeaningStudentCampusYearLevelHouseTutorFormLeavingDateSurnamePreferredGiven1
293801Leonor Wilber NEWNEWI5MITI-05AWilber Leonor Leonor
379718Tandra Trosper NORNORMALI11CAWCAW06I-11JTrosper Tandra Tandra
457633Iva Montag NORNORMALI11MITMIT09I-11GMontag Iva Iva
557634Isaura Mccoll NORNORMALI9MITMIT08I-09JMccoll Isaura Isaura
685148Doreatha Marrs NORNORMALI5LEII-05DMarrs Doreatha Doreatha
785147Golda Drumm NORNORMALI8CUNCUN01I-08GDrumm Golda Golda
878145Sol Frederic NORNORMALI7CUNCUN10I-07AFrederic Sol Sol
992329Kristin Ansley NEWNEWI0CUNI-PRBAnsley Kristin Kristin
1078203Onie Seman NEWNEWI7BRDBRD01I-07ASeman Onie Onie
1178202Len Schimke NORNORMALI10BRDBRD01I-10JSchimke Len Len
1286696Waylon Mong NORNORMALI11MITMIT08I-11JMong Waylon Waylon
1389126Timmy Urick NORNORMALI6MITI-06BUrick Timmy Timmy
1474751Marcie Heberling NORNORMALI4LEII-04CHeberling Marcie Marcie
1567748Sandy Murtagh NORNORMALI6LEII-06CMurtagh Sandy Sandy
1692340Deane Ikner NEWNEWI5LEII-05AIkner Deane Deane
1780217Chad Prince NORNORMALI4LEII-04CPrince Chad Chad
1867596Darryl Rudisill NORNORMALI6LEII-06ARudisill Darryl Darryl
1989630Rory Daw NEWNEWI8CHICHI06I-08EDaw Rory Rory
2080494Roseline Caiazzo NORNORMALI4LEII-04ACaiazzo Roseline Roseline
2167836Timika Lomeli NORNORMALI8LEILEI01I-08ELomeli Timika Timika
2252685Omega Paulding NORNORMALI12MITMIT01I-12BPaulding Omega Omega
2382396Lorenza Hildreth NORNORMALI11BRDBRD06I-11LHildreth Lorenza Lorenza
2478548Tashina Deltoro NEWNEWI11GOUGOU06I-11GDeltoro Tashina Tashina
2587696Ona Vandergriff NORNORMALI7LVRLAV10I-07BVandergriff Ona Ona
2669178Willy Chadbourne NORNORMALI5MITI-05DChadbourne Willy Willy
2775774Florine Culley NORNORMALI9SUTSUT01I-09ECulley Florine Florine
2877711Clarita Buffington NORNORMALI6LEII-06ABuffington Clarita Clarita
2977710Marcelo Lozoya NORNORMALI9BRDBRD07I-09CLozoya Marcelo Marcelo
3085464Kati Leedom NORNORMALI9BRDBRD09I-09FLeedom Kati Kati
3185125Cesar Chalk NORNORMALI5CUNI-05BChalk Cesar Cesar
3277994Asha Moone NORNORMALI11BRDBRD02I-11MMoone Asha Asha
3386459Deloris Prill NORNORMALI11BRDBRD04I-11KPrill Deloris Deloris
3485122Gayla Rm NORNORMALI7CAWCAW01I-07CRm Gayla Gayla
3590377Richelle Mckelvy NEWNEWI10BRDI-10MMckelvy Richelle Richelle
3675242Shantell Wehmeyer NORNORMALI11GOUGOU04I-11EWehmeyer Shantell Shantell
3789996Annabelle Cluff NORNORMALI8CAWCAW01I-08DCluff Annabelle Annabelle
3874945Edwina Kellam NORNORMALI11CHICHI01I-11MKellam Edwina Edwina
3994481Cori Knicely NEWNEWI7LEILEI01I-07HKnicely Cori Cori
4094482Sharyl Amyx NEWNEWI4LEII-04BAmyx Sharyl Sharyl
4194477Kareem Leeper NEWNEWI9LEILEI03I-09BLeeper Kareem Kareem
4277577Zenia Camara NORNORMALI4LEII-04BCamara Zenia Zenia
4386723Breann Verdejo NORNORMALI1LEII-01AVerdejo Breann Breann
4462810Cythia Kersh NORNORMALI12MITMIT02I-12CKersh Cythia Cythia
4562811Addie Zartman NORNORMALI10MITMIT01I-10KZartman Addie Addie
4676554Quinton Benedict NORNORMALI8SUTSUT01I-08DBenedict Quinton Quinton
4777171Larita Bemis NORNORMALI5CUNI-05DBemis Larita Larita
4876553Boris Rickey NORNORMALI11SUTSUT01I-11FRickey Boris Boris
4990282Ilana Torry NORNORMALI8LEILEI02I-08ETorry Ilana Ilana
5094392Kerri Brumer NEWNEWI1LEII-01CBrumer Kerri Kerri
5179895Babara Lampton NORNORMALI4LEII-04BLampton Babara Babara
5286565Lizzie Spinelli NORNORMALI9LVRLAV01I-09LSpinelli Lizzie Lizzie
5392447Carroll Buckman NEWNEWI7LVRLAV10I-07KBuckman Carroll Carroll
5494309Nguyet Trudeau NEWNEWI5LEII-05CTrudeau Nguyet Nguyet
5580504Joslyn Rouillard NORNORMALI11BRDBRD05I-11ARouillard Joslyn Joslyn
5677566Livia Dinh NORNORMALI1MITI-01CDinh Livia Livia
5777213Mechelle Scarborough NORNORMALI3MITI-03CScarborough Mechelle Mechelle
5889736Candelaria Dunham NORNORMALI12CHICHI08I-12LDunham Candelaria Candelaria
5974470Man Gebhardt NEWNEWI7CUNCUN01I-07DGebhardt Man Man
6083839Elva Eastwood NORNORMALI11BRDBRD07I-11EEastwood Elva Elva
6161636Doris Sullens NEWNEWI7CUNCUN02I-07HSullens Doris Doris
6269204Aleen Lawley NORNORMALI7CUNCUN10I-07ELawley Aleen Aleen
6369205Leana Bosco NORNORMALI6CUNI-06DBosco Leana Leana
6485268Tawanna Scheurer NORNORMALI5LEII-05CScheurer Tawanna Tawanna
6585267Ezra Prow NORNORMALI10CHICHI01I-10LProw Ezra Ezra
6670907Cassey Spiro NEWNEWI7CAWCAW02I-07KSpiro Cassey Cassey
6762092Emilio Lama NORNORMALI9GOUGOU03I-09JLama Emilio Emilio
6892364Lura Stam NEWNEWI7BRDBRD02I-07AStam Lura Lura
6973859Angel Pettry NORNORMALI11BRDBRD09I-11CPettry Angel Angel
7073860Millicent Vancleave NORNORMALI8BRDBRD09I-08DVancleave Millicent Millicent
7187244Sade Bellinger NORNORMALI1CUNI-01ABellinger Sade Sade
7273370Brooke Earnhardt NORNORMALI4MITI-04BEarnhardt Brooke Brooke
7380860Norman Keels NORNORMALI2MITI-02AKeels Norman Norman
7466193Annice Yopp NORNORMALI6MITI-06EYopp Annice Annice
7587642Liane Ohearn NORNORMALI9CHICHI01I-09EOhearn Liane Liane
7689745Lesha Godlewski NORNORMALI8CHICHI02I-08CGodlewski Lesha Lesha
7764189Belva Rumore NORNORMALI9CUNCUN04I-09ARumore Belva Belva
7870512Burt Hagins NORNORMALI10LVRLAV01I-10CHagins Burt Burt
7973194Derek Robins NORNORMALI8CAWCAW05I-08ARobins Derek Derek
8073192Cathie Picone NORNORMALI10CAWCAW09I-10GPicone Cathie Cathie
8189867Horacio Beattie NORNORMALI1MITI-01BBeattie Horacio Horacio
8285774Lenita Shand NORNORMALI9BRDBRD04I-09BShand Lenita Lenita
8373512Jaclyn Yandell NORNORMALI12BRDBRD02I-12BYandell Jaclyn Jaclyn
8490912Kennith Prada NORNORMALI11MITMIT04I-11MPrada Kennith Kennith
8575314Tiny Nuzzo NORNORMALI12LEILEI03I-12DNuzzo Tiny Tiny
8682718Latricia Stepney NORNORMALI11LEILEI08I-11EStepney Latricia Latricia
8786882Jenell Appell NORNORMALI2MITI-02CAppell Jenell Jenell
8889726Oretha Montesdeoca NORNORMALI8BRDBRD09I-08CMontesdeoca Oretha Oretha
8981555Valene Springer NORNORMALI9GOUGOU03I-09ASpringer Valene Valene
9079540Jaqueline Marano NORNORMALI9BRDBRD09I-09FMarano Jaqueline Jaqueline
9179541Charise Vincent NORNORMALI6CUNI-06AVincent Charise Charise
9288800Jonna Asaro NEWNEWI0MITI-PRAAsaro Jonna Jonna
9367915Kelley Lambros NORNORMALI6MITI-06ELambros Kelley Kelley
9477784Felicia Cavitt NORNORMALI3MITI-03CCavitt Felicia Felicia
9569077Cruz Steedley NORNORMALI6MITI-06ASteedley Cruz Cruz
9659792Darrell Federico NORNORMALI10MITMIT02I-10JFederico Darrell Darrell
9755328Rolande Aguilera NORNORMALI12CUNCUN01I-12EAguilera Rolande Rolande
9884699Jean Spataro NORNORMALI10CUNCUN06I-10JSpataro Jean Jean
9947703Yuriko Allard NORNORMALI12BRDBRD03I-12CAllard Yuriko Yuriko
10059101Monica Ciriaco NORNORMALI12CHICHI01I-12DCiriaco Monica Monica
10170848Yvonne Tomberlin NORNORMALI7CHICHI04I-07CTomberlin Yvonne Yvonne
10268308Stefany Fetterman NORNORMALI9LEILEI02I-09AFetterman Stefany Stefany
10388507Jesse Becton NEWNEWI5LEII-05BBecton Jesse Jesse
10485698Aida Kopecky NORNORMALI12SUTSUT09I-12FKopecky Aida Aida
10568401Synthia Burke NORNORMALI9BRDBRD06I-09KBurke Synthia Synthia
10674054Kaitlin Castonguay NEWNEWI7BRDBRD06I-07FCastonguay Kaitlin Kaitlin
10745121Quentin Tritt NORNORMALI12SUTSUT10I-12ETritt Quentin Quentin
10879797Ranee Fane NEWNEWI7MITMIT01I-07FFane Ranee Ranee
10979798Markus Kocian NEWNEWI7MITMIT02I-07KKocian Markus Markus
11066046Callie Caballero NORNORMALI10MITMIT03I-10GCaballero Callie Callie
11190673Alyssa Crim NORNORMALI10CHICHI06I-10BCrim Alyssa Alyssa
11290674Lisha Tinkle NORNORMALI9CHICHI07I-09GTinkle Lisha Lisha
11390574Lovella Cordell NORNORMALI5MITI-05ACordell Lovella Lovella
11484723Wilda Netherton NORNORMALI9LVRLAV01I-09GNetherton Wilda Wilda
11590575Miquel Saddler NORNORMALI5MITI-05ASaddler Miquel Miquel
11679161Tifany Freyer NORNORMALI10SUTSUT02I-10CFreyer Tifany Tifany
11779163Theodore Kalis NORNORMALI8SUTSUT02I-08AKalis Theodore Theodore
11872744Elaine Flippo NORNORMALI9CAWCAW05I-09JFlippo Elaine Elaine
11980531Jarrett Bourbon NORNORMALI12GOUGOU05I-12GBourbon Jarrett Jarrett
12074460Kenneth Wiltz NORNORMALI10GOUGOU02I-10DWiltz Kenneth Kenneth
12167375Venetta Ball NORNORMALI10GOUGOU02I-10ABall Venetta Venetta
12267374Sina Broaden NORNORMALI12GOUGOU05I-12GBroaden Sina Sina
12361992Jenice Sorrels NORNORMALI11SUTSUT02I-11DSorrels Jenice Jenice
12458119Berta Smyre NORNORMALI11BRDBRD10I-11FSmyre Berta Berta
12558120Delma Viggiano NORNORMALI10BRDBRD03I-10FViggiano Delma Delma
12684747Nydia Zapata NORNORMALI9SUTSUT02I-09BZapata Nydia Nydia
12783298Paz Fretwell NORNORMALI10CAWCAW08I-10DFretwell Paz Paz
12875061Altha Chew NORNORMALI8LEILEI03I-08CChew Altha Altha
12964795Arlena Minjares NORNORMALI10LEILEI04I-10LMinjares Arlena Arlena
13058170Elvis Lenzen NORNORMALI12GOUGOU05I-12HLenzen Elvis Elvis
13187608Rolande Lamontagne NORNORMALI4CUNI-04CLamontagne Rolande Rolande
13290791Lisandra Cotten NORNORMALI5LEII-05CCotten Lisandra Lisandra
13391106Donn Rundell NORNORMALI2LEII-02BRundell Donn Donn
13472300Rossana Cassara NORNORMALI4LEII-04BCassara Rossana Rossana
13564726Altagracia Houpt NORNORMALI6LEII-06BHoupt Altagracia Altagracia
13682262Stacee Jackman NORNORMALI11LEILEI09I-11JJackman Stacee Stacee
13787050Katlyn Grasso NORNORMALI7BRDBRD03I-07KGrasso Katlyn Katlyn
13854238Shanice Wilkerson NORNORMALI11CAWCAW02I-11CWilkerson Shanice Shanice
13987831Matilde Elsworth NORNORMALI8GOUGOU01I-08GElsworth Matilde Matilde
14052718Elanor Mckissick NORNORMALI10GOUGOU03I-10AMckissick Elanor Elanor
14177770Irina Cota NORNORMALI5CUNI-05CCota Irina Irina
14277771Margo Kibler NORNORMALI3CUNI-03AKibler Margo Margo
14351192Royal Kinser NORNORMALI12CUNCUN10I-12EKinser Royal Royal
14490901Dana Meese NORNORMALI11LEILEI06I-11LMeese Dana Dana
14594220Kati Nilges NEWNEWI3MITI-03CNilges Kati Kati
14694114Joannie Aburto NEWNEWI5MITI-05DAburto Joannie Joannie
14791148Anya Gurney NORNORMALI10CUNCUN07I-10LGurney Anya Anya
14866759Lita Cristobal NORNORMALI11MITMIT07I-11KCristobal Lita Lita
14986334Maggie Weible NORNORMALI5LEII-05BWeible Maggie Maggie
15086333Ashlee Mead NORNORMALI7CHICHI10I-07GMead Ashlee Ashlee
15194200Dakota Gish NEWNEWI5CUNI-05DGish Dakota Dakota
15283439Joni Barnette NEWNEWI0LEII-PRCBarnette Joni Joni
15383440Dung Zimmerman NORNORMALI1LEII-01CZimmerman Dung Dung
15470747Lonny Pantaleo NORNORMALI6MITI-06EPantaleo Lonny Lonny
15590642Jeanna Feddersen NEWNEWI8CUNCUN03I-08JFeddersen Jeanna Jeanna
15690598Miranda Oren NORNORMALI11MITMIT10I-11AOren Miranda Miranda
15780131Lisandra Saiki NORNORMALI4CUNI-04ASaiki Lisandra Lisandra
15888476Maurita Rutter NORNORMALI8LEILEI04I-08FRutter Maurita Maurita
15975728Abdul Spagnoli NORNORMALI6CUNI-06ESpagnoli Abdul Abdul
16071408Antwan Shrewsbury NORNORMALI7GOUGOU10I-07GShrewsbury Antwan Antwan
16167048Hyon Zynda NORNORMALI10GOUGOU03I-10MZynda Hyon Hyon
16283572Lahoma Izaguirre NORNORMALI3CUNI-03CIzaguirre Lahoma Lahoma
16388266Pandora Blattner NORNORMALI8MITMIT01I-08CBlattner Pandora Pandora
16451769Al Probst NORNORMALI10MITMIT04I-10LProbst Al Al
16554528Carol Feiler NORNORMALI12BRDBRD04I-12HFeiler Carol Carol
16683609Tomeka Emond NORNORMALI10LEILEI05I-10KEmond Tomeka Tomeka
16749059Lovella Sill NORNORMALI11BRDBRD04I-11KSill Lovella Lovella
16879625Federico Kaina NORNORMALI7GOUGOU10I-07CKaina Federico Federico
16963371Celena Jacquez NORNORMALI10BRDI-10LJacquez Celena Celena
17063372Tyrone Lemmons NEWNEWI9BRDI-09MLemmons Tyrone Tyrone
17193935Dominga Mccary NEWNEWI11GOUGOU03I-11FMccary Dominga Dominga
17285517Josefina Lafferty NORNORMALI11CAWCAW08I-11CLafferty Josefina Josefina
17390534Tenisha Devito NEWNEWI7GOUGOU10I-07CDevito Tenisha Tenisha
17465189Cathie Goodridge NORNORMALI11GOUGOU04I-11JGoodridge Cathie Cathie
17590533Genevieve Stringham NORNORMALI8GOUGOU01I-08AStringham Genevieve Genevieve
17671465Ena Drumm NORNORMALI6CUNI-06BDrumm Ena Ena
17771464Edna Tolan NORNORMALI10BRDBRD03I-10JTolan Edna Edna
17869659Claudette Sheets NORNORMALI8GOUGOU01I-08JSheets Claudette Claudette
17992622Williemae Gildea NORNORMALI9CHICHI02I-09BGildea Williemae Williemae
18091856Genny Suire NORNORMALI11LVRLAV06I-11HSuire Genny Genny
18176276Karleen Marsland NORNORMALI8MITMIT02I-08DMarsland Karleen Karleen
18292588Rolf Sartwell NORNORMALI11BRDI-11BSartwell Rolf Rolf
18388084Starr Walz NORNORMALI5MITI-05BWalz Starr Starr
18479896Leif Falls NORNORMALI11CUNCUN04I-11DFalls Leif Leif
18577789Nicolas Spillane NORNORMALI12CAWCAW02I-12JSpillane Nicolas Nicolas
18693580Classie Cardin NEWNEWI10LEILEI03I-10JCardin Classie Classie
18793578Laurette Baize NEWNEWI11LEILEI05I-11FBaize Laurette Laurette
18882550Kevin Beltran NORNORMALI9CAWCAW02I-09GBeltran Kevin Kevin
18990460Maira Kelley NORNORMALI8LEILEI05I-08KKelley Maira Maira
19086830Waneta Stack NORNORMALI9LEILEI03I-09KStack Waneta Waneta
19172856Herbert Bombard NORNORMALI12SUTSUT07I-12HBombard Herbert Herbert
19289775Nick Feemster REPREPEATINGI0LEII-PRCFeemster Nick Nick
19379248Ayanna Salzman NORNORMALI9GOUGOU04I-09ASalzman Ayanna Ayanna
19480689Ed Folkers NORNORMALI7GOUGOU01I-07AFolkers Ed Ed
19586979Mozella Angstadt NORNORMALI12CUNCUN01I-12KAngstadt Mozella Mozella
19677688Cara Shuffler NORNORMALI12CHICHI02I-12LShuffler Cara Cara
19775054Jaqueline Hepworth NORNORMALI6CUNI-06EHepworth Jaqueline Jaqueline
19891859Shelley Redel NORNORMALI10CAWCAW10I-10MRedel Shelley Shelley
19947891Rose Beecroft NORNORMALI12CUNCUN02I-12GBeecroft Rose Rose
20078757Gregory Overfield NORNORMALI9BRDBRD08I-09DOverfield Gregory Gregory
20188137Sheridan Blye NEWNEWI3LEII-03BBlye Sheridan Sheridan
20286376Tresa Blatt NEWNEWI7CHICHI02I-07BBlatt Tresa Tresa
20377108Delilah Hartigan NORNORMALI9BRDBRD09I-09AHartigan Delilah Delilah
20469959Danielle Scurry NORNORMALI11LVRLAV01I-11BScurry Danielle Danielle
20577570Haydee Garnica NEWNEWI7SUTSUT01I-07DGarnica Haydee Haydee
20677569Florencio Mcconn NORNORMALI10SUTSUT03I-10KMcconn Florencio Florencio
20770887Alena Gotcher NORNORMALI11CUNCUN01I-11EGotcher Alena Alena
20887216Shona Cozad NORNORMALI9LEILEI04I-09ECozad Shona Shona
20982898Delmar Stultz NORNORMALI10LEILEI06I-10BStultz Delmar Delmar
21082732Jong Kenton NORNORMALI11MITMIT06I-11JKenton Jong Jong
21182918Joanna Glascock NEWNEWI7BRDBRD02I-07JGlascock Joanna Joanna
21292972Joellen Schaible NEWNEWI11SUTSUT06I-11LSchaible Joellen Joellen
21367206Rosana Villalvazo NORNORMALI10SUTSUT10I-10KVillalvazo Rosana Rosana
21472376Shelley Karam NORNORMALI9CUNCUN02I-09CKaram Shelley Shelley
21562586Karol Kiely NORNORMALI12CUNCUN01I-12BKiely Karol Karol
21684953Chris Wimbish NORNORMALI9CHICHI05I-09DWimbish Chris Chris
21787862Theodora Franks NORNORMALI10LEILEI07I-10DFranks Theodora Theodora
21887861Aline Dollar NORNORMALI12BRDBRD06I-12MDollar Aline Aline
21960405Clair Hallford NORNORMALI11CAWCAW02I-11BHallford Clair Clair
22087680Fletcher Samaniego NORNORMALI8CAWCAW01I-08FSamaniego Fletcher Fletcher
22151918Jeri Gress NORNORMALI11CUNCUN10I-11KGress Jeri Jeri
22273062Marnie Leeder NORNORMALI10GOUGOU03I-10DLeeder Marnie Marnie
22373061Maryanne Ried NORNORMALI11GOUGOU04I-11FRied Maryanne Maryanne
22473064Nathanael Colman NORNORMALI4MITI-04AColman Nathanael Nathanael
22573063Erna Hodnett NORNORMALI8GOUGOU02I-08JHodnett Erna Erna
22683323Inell Barnhardt NEWNEWI0MITI-PRABarnhardt Inell Inell
22783563Rona Blunk NEWNEWI7MITMIT03I-07CBlunk Rona Rona
22883565Wenona Petrarca NEWNEWI5MITI-05BPetrarca Wenona Wenona
22983562Gala Skiba NORNORMALI9MITMIT09I-09BSkiba Gala Gala
Full School List
Cell Formulas
RangeFormula
K2:K229K2=RIGHT(B2,LEN(B2)-FIND(" ",B2,1))
L2:L229L2=M2
M2:M229M2=LEFT(B2,FIND(" ",B2,1))
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows

ADVERTISEMENT

Thanks for that, but unfortunately it only goes up to col M, but you want to compare data in col S.
Can you supply another sample, it doesn't need to be as many rows.
Also what version of Xl are you using? You can update your profile to display permanently.
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
383
Office Version
  1. 2016
Platform
  1. Windows

ADVERTISEMENT

Book1
ABCDEFGHIJKLMNOPQRS
1IDStudent NameStatusSynergyMeaningStudentCampusYearLevelHouseTutorFormLeavingDateSurnamePreferredGiven1LeavingHighlightFlagLeftFlagLOAFlagGovernmentStudentNumberBoarderGender
293801Leonor Wilber NEWNEWI5MITI-05AWilber Leonor Leonor FALSEFALSEDF
379718Tandra Trosper NORNORMALI11CAWCAW06I-11JTrosper Tandra Tandra Tandra FALSEFALSE8126238099BM
457633Iva Montag NORNORMALI11MITMIT09I-11GMontag Iva Iva Iva FALSEFALSE2312705672DF
557634Isaura Mccoll NORNORMALI9MITMIT08I-09JMccoll Isaura Isaura Isaura FALSEFALSEDM
685148Doreatha Marrs NORNORMALI5LEII-05DMarrs Doreatha Doreatha Doreatha FALSEFALSEDM
785147Golda Drumm NORNORMALI8CUNCUN01I-08GDrumm Golda Golda Golda FALSEFALSEDF
878145Sol Frederic NORNORMALI7CUNCUN10I-07AFrederic Sol Sol Sol FALSEFALSEDM
992329Kristin Ansley NEWNEWI0CUNI-PRBAnsley Kristin Kristin Kristin FALSEFALSEDF
1078203Onie Seman NEWNEWI7BRDBRD01I-07ASeman Onie Onie Onie FALSEFALSEDF
1178202Len Schimke NORNORMALI10BRDBRD01I-10JSchimke Len Len Len FALSEFALSEDF
1286696Waylon Mong NORNORMALI11MITMIT08I-11JMong Waylon Waylon Waylon FALSEFALSE4600142600DM
1389126Timmy Urick NORNORMALI6MITI-06BUrick Timmy Timmy Timmy FALSEFALSEDM
1474751Marcie Heberling NORNORMALI4LEII-04CHeberling Marcie Marcie Marcie FALSEFALSEDM
1567748Sandy Murtagh NORNORMALI6LEII-06CMurtagh Sandy Sandy Sandy FALSEFALSEDF
1692340Deane Ikner NEWNEWI5LEII-05AIkner Deane Deane Deane FALSEFALSEDM
1780217Chad Prince NORNORMALI4LEII-04CPrince Chad Chad Chad FALSEFALSEDF
1867596Darryl Rudisill NORNORMALI6LEII-06ARudisill Darryl Darryl Darryl FALSEFALSEDM
1989630Rory Daw NEWNEWI8CHICHI06I-08EDaw Rory Rory Rory FALSEFALSEDM
2080494Roseline Caiazzo NORNORMALI4LEII-04ACaiazzo Roseline Roseline Roseline FALSEFALSEDM
2167836Timika Lomeli NORNORMALI8LEILEI01I-08ELomeli Timika Timika Timika FALSEFALSEDF
2252685Omega Paulding NORNORMALI12MITMIT01I-12BPaulding Omega Omega Omega FALSEFALSE6408213970DM
2382396Lorenza Hildreth NORNORMALI11BRDBRD06I-11LHildreth Lorenza Lorenza Lorenza FALSEFALSE8126238321BM
2478548Tashina Deltoro NEWNEWI11GOUGOU06I-11GDeltoro Tashina Tashina Tashina FALSEFALSE4600205050DM
2587696Ona Vandergriff NORNORMALI7LVRLAV10I-07BVandergriff Ona Ona Ona FALSEFALSEDM
2669178Willy Chadbourne NORNORMALI5MITI-05DChadbourne Willy Willy Willy FALSEFALSEDM
2775774Florine Culley NORNORMALI9SUTSUT01I-09ECulley Florine Florine Florine FALSEFALSEDM
2877711Clarita Buffington NORNORMALI6LEII-06ABuffington Clarita Clarita Clarita FALSEFALSEDF
2977710Marcelo Lozoya NORNORMALI9BRDBRD07I-09CLozoya Marcelo Marcelo Marcelo FALSEFALSEDM
3085464Kati Leedom NORNORMALI9BRDBRD09I-09FLeedom Kati Kati Kati FALSEFALSEDM
3185125Cesar Chalk NORNORMALI5CUNI-05BChalk Cesar Cesar Cesar FALSEFALSEDM
3277994Asha Moone NORNORMALI11BRDBRD02I-11MMoone Asha Asha Asha FALSEFALSE0005003561DF
3386459Deloris Prill NORNORMALI11BRDBRD04I-11KPrill Deloris Deloris Deloris FALSEFALSE2312705540BF
3485122Gayla Rm NORNORMALI7CAWCAW01I-07CRm Gayla Gayla Gayla FALSEFALSEDM
3590377Richelle Mckelvy NEWNEWI10BRDI-10MMckelvy Richelle Richelle Richelle FALSEFALSEDF
3675242Shantell Wehmeyer NORNORMALI11GOUGOU04I-11EWehmeyer Shantell Shantell Shantell FALSEFALSE4600142709DM
3789996Annabelle Cluff NORNORMALI8CAWCAW01I-08DCluff Annabelle Annabelle Annabelle FALSEFALSEDF
3874945Edwina Kellam NORNORMALI11CHICHI01I-11MKellam Edwina Edwina Edwina FALSEFALSE6408421441DM
3994481Cori Knicely NEWNEWI7LEILEI01I-07HKnicely Cori Cori Cori FALSEFALSEDF
4094482Sharyl Amyx NEWNEWI4LEII-04BAmyx Sharyl Sharyl Sharyl FALSEFALSEDM
4194477Kareem Leeper NEWNEWI9LEILEI03I-09BLeeper Kareem Kareem Kareem FALSEFALSEDF
4277577Zenia Camara NORNORMALI4LEII-04BCamara Zenia Zenia Zenia FALSEFALSEDF
4386723Breann Verdejo NORNORMALI1LEII-01AVerdejo Breann Breann Breann FALSEFALSEDF
4462810Cythia Kersh NORNORMALI12MITMIT02I-12CKersh Cythia Cythia Cythia FALSEFALSE6408213988DM
4562811Addie Zartman NORNORMALI10MITMIT01I-10KZartman Addie Addie Addie FALSEFALSEDF
4676554Quinton Benedict NORNORMALI8SUTSUT01I-08DBenedict Quinton Quinton Quinton FALSEFALSEDM
4777171Larita Bemis NORNORMALI5CUNI-05DBemis Larita Larita Larita FALSEFALSEDF
4876553Boris Rickey NORNORMALI11SUTSUT01I-11FRickey Boris Boris Boris FALSEFALSE6408421581DM
4990282Ilana Torry NORNORMALI8LEILEI02I-08ETorry Ilana Ilana Ilana FALSEFALSEDM
5094392Kerri Brumer NEWNEWI1LEII-01CBrumer Kerri Kerri Kerri FALSEFALSEDF
5179895Babara Lampton NORNORMALI4LEII-04BLampton Babara Babara Babara FALSEFALSEDM
5286565Lizzie Spinelli NORNORMALI9LVRLAV01I-09LSpinelli Lizzie Lizzie Lizzie FALSEFALSEDM
5392447Carroll Buckman NEWNEWI7LVRLAV10I-07KBuckman Carroll Carroll Carroll FALSEFALSEDF
5494309Nguyet Trudeau NEWNEWI5LEII-05CTrudeau Nguyet Nguyet Nguyet FALSEFALSEDM
5580504Joslyn Rouillard NORNORMALI11BRDBRD05I-11ARouillard Joslyn Joslyn Joslyn FALSEFALSE6408421912DM
5677566Livia Dinh NORNORMALI1MITI-01CDinh Livia Livia Livia FALSEFALSEDM
5777213Mechelle Scarborough NORNORMALI3MITI-03CScarborough Mechelle Mechelle Mechelle FALSEFALSEDM
5889736Candelaria Dunham NORNORMALI12CHICHI08I-12LDunham Candelaria Candelaria Candelaria FALSEFALSE2312503044BF
5974470Man Gebhardt NEWNEWI7CUNCUN01I-07DGebhardt Man Man Man FALSEFALSEDF
6083839Elva Eastwood NORNORMALI11BRDBRD07I-11EEastwood Elva Elva Elva FALSEFALSE4600142857DM
6161636Doris Sullens NEWNEWI7CUNCUN02I-07HSullens Doris Doris Doris FALSEFALSEDF
6269204Aleen Lawley NORNORMALI7CUNCUN10I-07ELawley Aleen Aleen Aleen FALSEFALSEDF
6369205Leana Bosco NORNORMALI6CUNI-06DBosco Leana Leana Leana FALSEFALSEDM
6485268Tawanna Scheurer NORNORMALI5LEII-05CScheurer Tawanna Tawanna Tawanna FALSEFALSEDM
6585267Ezra Prow NORNORMALI10CHICHI01I-10LProw Ezra Ezra Ezra FALSEFALSEDM
6670907Cassey Spiro NEWNEWI7CAWCAW02I-07KSpiro Cassey Cassey Cassey FALSEFALSEDM
6762092Emilio Lama NORNORMALI9GOUGOU03I-09JLama Emilio Emilio Emilio FALSEFALSEDF
6892364Lura Stam NEWNEWI7BRDBRD02I-07AStam Lura Lura Lura FALSEFALSEDM
6973859Angel Pettry NORNORMALI11BRDBRD09I-11CPettry Angel Angel Angel FALSEFALSE4600142428DF
7073860Millicent Vancleave NORNORMALI8BRDBRD09I-08DVancleave Millicent Millicent Millicent FALSEFALSEDM
7187244Sade Bellinger NORNORMALI1CUNI-01ABellinger Sade Sade Sade FALSEFALSEDF
7273370Brooke Earnhardt NORNORMALI4MITI-04BEarnhardt Brooke Brooke Brooke FALSEFALSEDM
7380860Norman Keels NORNORMALI2MITI-02AKeels Norman Norman Norman FALSEFALSEDF
7466193Annice Yopp NORNORMALI6MITI-06EYopp Annice Annice Annice FALSEFALSEDM
7587642Liane Ohearn NORNORMALI9CHICHI01I-09EOhearn Liane Liane Liane FALSEFALSEDM
7689745Lesha Godlewski NORNORMALI8CHICHI02I-08CGodlewski Lesha Lesha Lesha FALSEFALSEDF
7764189Belva Rumore NORNORMALI9CUNCUN04I-09ARumore Belva Belva Belva FALSEFALSEDF
7870512Burt Hagins NORNORMALI10LVRLAV01I-10CHagins Burt Burt Burt FALSEFALSEDF
7973194Derek Robins NORNORMALI8CAWCAW05I-08ARobins Derek Derek Derek FALSEFALSEDM
8073192Cathie Picone NORNORMALI10CAWCAW09I-10GPicone Cathie Cathie Cathie FALSEFALSEDF
8189867Horacio Beattie NORNORMALI1MITI-01BBeattie Horacio Horacio Horacio FALSEFALSEDM
8285774Lenita Shand NORNORMALI9BRDBRD04I-09BShand Lenita Lenita Lenita FALSEFALSEDF
8373512Jaclyn Yandell NORNORMALI12BRDBRD02I-12BYandell Jaclyn Jaclyn Jaclyn FALSEFALSE2312495860DF
8490912Kennith Prada NORNORMALI11MITMIT04I-11MPrada Kennith Kennith Kennith FALSEFALSE6408421748DF
8575314Tiny Nuzzo NORNORMALI12LEILEI03I-12DNuzzo Tiny Tiny Tiny FALSEFALSE2312495878DM
8682718Latricia Stepney NORNORMALI11LEILEI08I-11EStepney Latricia Latricia Latricia FALSEFALSE6408421961DF
8786882Jenell Appell NORNORMALI2MITI-02CAppell Jenell Jenell Jenell FALSEFALSEDM
8889726Oretha Montesdeoca NORNORMALI8BRDBRD09I-08CMontesdeoca Oretha Oretha Oretha FALSEFALSEDF
8981555Valene Springer NORNORMALI9GOUGOU03I-09ASpringer Valene Valene Valene FALSEFALSEDM
9079540Jaqueline Marano NORNORMALI9BRDBRD09I-09FMarano Jaqueline Jaqueline Jaqueline FALSEFALSEDM
9179541Charise Vincent NORNORMALI6CUNI-06AVincent Charise Charise Charise FALSEFALSEDF
9288800Jonna Asaro NEWNEWI0MITI-PRAAsaro Jonna Jonna Jonna FALSEFALSEDF
9367915Kelley Lambros NORNORMALI6MITI-06ELambros Kelley Kelley Kelley FALSEFALSEDM
9477784Felicia Cavitt NORNORMALI3MITI-03CCavitt Felicia Felicia Felicia FALSEFALSEDM
9569077Cruz Steedley NORNORMALI6MITI-06ASteedley Cruz Cruz Cruz FALSEFALSEDF
9659792Darrell Federico NORNORMALI10MITMIT02I-10JFederico Darrell Darrell Darrell FALSEFALSEDF
9755328Rolande Aguilera NORNORMALI12CUNCUN01I-12EAguilera Rolande Rolande Rolande FALSEFALSE6408213996DM
9884699Jean Spataro NORNORMALI10CUNCUN06I-10JSpataro Jean Jean Jean FALSEFALSEDM
9947703Yuriko Allard NORNORMALI12BRDBRD03I-12CAllard Yuriko Yuriko Yuriko FALSEFALSE2312495886DF
10059101Monica Ciriaco NORNORMALI12CHICHI01I-12DCiriaco Monica Monica Monica FALSEFALSE8126031585DF
10170848Yvonne Tomberlin NORNORMALI7CHICHI04I-07CTomberlin Yvonne Yvonne Yvonne FALSEFALSEDM
10268308Stefany Fetterman NORNORMALI9LEILEI02I-09AFetterman Stefany Stefany Stefany FALSEFALSEDF
10388507Jesse Becton NEWNEWI5LEII-05BBecton Jesse Jesse Jesse FALSEFALSEDM
10485698Aida Kopecky NORNORMALI12SUTSUT09I-12FKopecky Aida Aida Aida FALSEFALSE4599933787DM
10568401Synthia Burke NORNORMALI9BRDBRD06I-09KBurke Synthia Synthia Synthia FALSEFALSEDF
10674054Kaitlin Castonguay NEWNEWI7BRDBRD06I-07FCastonguay Kaitlin Kaitlin Kaitlin FALSEFALSEDM
10745121Quentin Tritt NORNORMALI12SUTSUT10I-12ETritt Quentin Quentin Quentin FALSEFALSE0004796629DF
10879797Ranee Fane NEWNEWI7MITMIT01I-07FFane Ranee Ranee Ranee FALSEFALSEBM
10979798Markus Kocian NEWNEWI7MITMIT02I-07KKocian Markus Markus Markus FALSEFALSEBF
11066046Callie Caballero NORNORMALI10MITMIT03I-10GCaballero Callie Callie Callie FALSEFALSEDM
11190673Alyssa Crim NORNORMALI10CHICHI06I-10BCrim Alyssa Alyssa Alyssa FALSEFALSEDF
11290674Lisha Tinkle NORNORMALI9CHICHI07I-09GTinkle Lisha Lisha Lisha FALSEFALSEDF
11390574Lovella Cordell NORNORMALI5MITI-05ACordell Lovella Lovella Lovella FALSEFALSEDM
11484723Wilda Netherton NORNORMALI9LVRLAV01I-09GNetherton Wilda Wilda Wilda FALSEFALSEDF
11590575Miquel Saddler NORNORMALI5MITI-05ASaddler Miquel Miquel Miquel FALSEFALSEDM
11679161Tifany Freyer NORNORMALI10SUTSUT02I-10CFreyer Tifany Tifany Tifany FALSEFALSEDF
11779163Theodore Kalis NORNORMALI8SUTSUT02I-08AKalis Theodore Theodore Theodore FALSEFALSEDM
11872744Elaine Flippo NORNORMALI9CAWCAW05I-09JFlippo Elaine Elaine Elaine FALSEFALSEDF
11980531Jarrett Bourbon NORNORMALI12GOUGOU05I-12GBourbon Jarrett Jarrett Jarrett FALSEFALSE0004796637DF
12074460Kenneth Wiltz NORNORMALI10GOUGOU02I-10DWiltz Kenneth Kenneth Kenneth FALSEFALSEDM
12167375Venetta Ball NORNORMALI10GOUGOU02I-10ABall Venetta Venetta Venetta FALSEFALSEDF
12267374Sina Broaden NORNORMALI12GOUGOU05I-12GBroaden Sina Sina Sina FALSEFALSE6408214002DM
12361992Jenice Sorrels NORNORMALI11SUTSUT02I-11DSorrels Jenice Jenice Jenice FALSEFALSE6408421680DM
12458119Berta Smyre NORNORMALI11BRDBRD10I-11FSmyre Berta Berta Berta FALSEFALSE2312705482DM
12558120Delma Viggiano NORNORMALI10BRDBRD03I-10FViggiano Delma Delma Delma FALSEFALSEDF
12684747Nydia Zapata NORNORMALI9SUTSUT02I-09BZapata Nydia Nydia Nydia FALSEFALSEDM
12783298Paz Fretwell NORNORMALI10CAWCAW08I-10DFretwell Paz Paz Paz FALSEFALSEBF
12875061Altha Chew NORNORMALI8LEILEI03I-08CChew Altha Altha Altha FALSEFALSEDF
12964795Arlena Minjares NORNORMALI10LEILEI04I-10LMinjares Arlena Arlena Arlena FALSEFALSEDM
13058170Elvis Lenzen NORNORMALI12GOUGOU05I-12HLenzen Elvis Elvis Elvis FALSEFALSE0004796645DM
13187608Rolande Lamontagne NORNORMALI4CUNI-04CLamontagne Rolande Rolande Rolande FALSEFALSEDM
13290791Lisandra Cotten NORNORMALI5LEII-05CCotten Lisandra Lisandra Lisandra FALSEFALSEDF
13391106Donn Rundell NORNORMALI2LEII-02BRundell Donn Donn Donn FALSEFALSEDM
13472300Rossana Cassara NORNORMALI4LEII-04BCassara Rossana Rossana Rossana FALSEFALSEDF
13564726Altagracia Houpt NORNORMALI6LEII-06BHoupt Altagracia Altagracia Altagracia FALSEFALSEDM
13682262Stacee Jackman NORNORMALI11LEILEI09I-11JJackman Stacee Stacee Stacee FALSEFALSE6408421938BF
13787050Katlyn Grasso NORNORMALI7BRDBRD03I-07KGrasso Katlyn Katlyn Katlyn FALSEFALSEDF
13854238Shanice Wilkerson NORNORMALI11CAWCAW02I-11CWilkerson Shanice Shanice Shanice FALSEFALSE4600142493DM
13987831Matilde Elsworth NORNORMALI8GOUGOU01I-08GElsworth Matilde Matilde Matilde FALSEFALSEDF
14052718Elanor Mckissick NORNORMALI10GOUGOU03I-10AMckissick Elanor Elanor Elanor FALSEFALSEDM
14177770Irina Cota NORNORMALI5CUNI-05CCota Irina Irina Irina FALSEFALSEDF
14277771Margo Kibler NORNORMALI3CUNI-03AKibler Margo Margo Margo FALSEFALSEDM
14351192Royal Kinser NORNORMALI12CUNCUN10I-12EKinser Royal Royal Royal FALSEFALSE6408214010DM
14490901Dana Meese NORNORMALI11LEILEI06I-11LMeese Dana Dana Dana FALSEFALSE4600142758DM
14594220Kati Nilges NEWNEWI3MITI-03CNilges Kati Kati Kati FALSEFALSEDM
14694114Joannie Aburto NEWNEWI5MITI-05DAburto Joannie Joannie Joannie FALSEFALSEDF
14791148Anya Gurney NORNORMALI10CUNCUN07I-10LGurney Anya Anya Anya FALSEFALSEDM
14866759Lita Cristobal NORNORMALI11MITMIT07I-11KCristobal Lita Lita Lita FALSEFALSE6408421516DM
14986334Maggie Weible NORNORMALI5LEII-05BWeible Maggie Maggie Maggie FALSEFALSEDM
15086333Ashlee Mead NORNORMALI7CHICHI10I-07GMead Ashlee Ashlee Ashlee FALSEFALSEDF
15194200Dakota Gish NEWNEWI5CUNI-05DGish Dakota Dakota Dakota FALSEFALSEDM
15283439Joni Barnette NEWNEWI0LEII-PRCBarnette Joni Joni Joni FALSEFALSEDF
15383440Dung Zimmerman NORNORMALI1LEII-01CZimmerman Dung Dung Dung FALSEFALSEDM
15470747Lonny Pantaleo NORNORMALI6MITI-06EPantaleo Lonny Lonny Lonny FALSEFALSEDF
15590642Jeanna Feddersen NEWNEWI8CUNCUN03I-08JFeddersen Jeanna Jeanna Jeanna FALSEFALSEBM
15690598Miranda Oren NORNORMALI11MITMIT10I-11AOren Miranda Miranda Miranda FALSEFALSE2312705490DM
15780131Lisandra Saiki NORNORMALI4CUNI-04ASaiki Lisandra Lisandra Lisandra FALSEFALSEDM
Full School List
 

Fluff

MrExcel MVP, Moderator
Joined
Jun 12, 2014
Messages
62,252
Office Version
  1. 365
Platform
  1. Windows
🍻
With that data is this what you expect?
ZFluff.xlsm
ABCDE
1
2
3
4
57788
6Fmfm
7140
8Yr 7 GirlsYr 7 Boys
9SemanFredericDrummDaw
10KnicelyVandergriffLomeliBenedict
11BuckmanRmCluffTorry
12GebhardtSpiroGodlewskiVancleave
13SullensStamMontesdeocaRobins
14LawleyTomberlinChewKalis
15KocianCastonguayElsworthFeddersen
16GrassoFane
17Mead
18
Finals
Cell Formulas
RangeFormula
B9:B17,D9:E15,C9:C16B9=FILTER('Full School List'!$K$2:$K$157,('Full School List'!$F$2:$F$157=B5)*('Full School List'!$S$2:$S$157=B6))
Dynamic array formulas.
 

J.Ty.

Well-known Member
Joined
Feb 4, 2012
Messages
1,118
Office Version
  1. 365
  2. 2013
  3. 2010
Platform
  1. Windows
  2. Web
Here is my spreadsheet with my formula. It seems to work correctly. Please test it online here.

paleontology.xlsx
BCDEFGHIJKLM
2BRD
3
4
57
6f
7
8
9
10SemanSeman
11GrassoGrasso
12 
13 
Sheet2
Cell Formulas
RangeFormula
D10:D11D10=FILTER('Full School List'!$K$2:$K$150,('Full School List'!$F$2:$F$150=Sheet2!C$5)*('Full School List'!G2:G150=Sheet2!$M$2)*('Full School List'!$S$2:$S$150=Sheet2!$C$6))
C10:C13C10=IFERROR(INDEX('Full School List'!$K$2:$K$150, MATCH(0, (COUNTIF(C$9:C9, 'Full School List'!$K$2:$K$150)=COUNTIF( 'Full School List'!$K$2:$K$150, 'Full School List'!$K$2:$K$150))+IF('Full School List'!$F$2:$F$150<>C$5, 1, 0)+IF('Full School List'!$S$2:$S$150<>C$6, 1, 0)+IF('Full School List'!$G$2:$G$150<>$M$2, 1, 0), 0)),"")
Press CTRL+SHIFT+ENTER to enter array formulas surrounded with curly braces.
Dynamic array formulas.
 

palaeontology

Active Member
Joined
May 12, 2017
Messages
383
Office Version
  1. 2016
Platform
  1. Windows
Unfortunately, I had no success with those suggestions.

I have however made a formula that does return what I needed ...

{=IFERROR(INDEX('Full School List'!$L$2:$L$2500,SMALL(IF(('Full School List'!$F$2:$F$2500=B$5)*('Full School List'!$S$2:$S$2500=B$6)*('Full School List'!$G$2:$G$2500=$E$2),ROW('Full School List'!$L$2:$L$2500)),ROW(1:1))-1,1)&" "&INDEX('Full School List'!$K$2:$K$2500,SMALL(IF(('Full School List'!$F$2:$F$2500=B$5)*('Full School List'!$S$2:$S$2500=B$6)*('Full School List'!$G$2:$G$2500=$E$2),ROW('Full School List'!$K$2:$K$2500)),ROW(1:1))-1,1),"")}

Thankyou for all your suggestions.

We got there in the end,

Kindest regards,

Chris
 

Forum statistics

Threads
1,141,060
Messages
5,704,037
Members
421,323
Latest member
Exidous

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