Determine whether an alpha value falls within a range...

[merlinCLT]

Greetings, All,

I am using two spreadsheets consisting of the following fields:

1) High school students' school name, first and last names
2) Counselor names and caseload alphabetical ranges

I am trying to create a formula to populate the school counselor's name for each respective student based on the assigned alpha named range for each counselor. Ultimately, I want to be able to mail merge student data with emails to school counselors for their reference.

The actual list I have includes 1000+ records and rather than manually enter the assigned counselor in each cell, I am hoping that someone may have an idea of how to create a function or VBA that would make this process more streamlined.

Student Data

Student_First	Student_Last	High_School	Assigned_Counselor
Adam	Brown	AKHS
Jessica	Brown	BHS
Tommy	Lee	VHS
Aziz	Ansari	AKHS
Michael	Scott	VHS
Howie	Pierson	BHS
Lila	Thurmond	THS

Counselor Data

Counselor_First	Counselor_Last	High_School	Caseload
Kelly	Thompson	AKHS	A-Moon
Mary	Black	BHS	A-Kit
Gary	Busey	BHS	Kita-Z
Bud	Shiffman	AKHS	Moona-Z
Scott	Williams	THS	All Students
Susie	Quintano	VHS	All Students

Desired Outcome

Student_First	Student_Last	High School	Assigned_Counselor
Adam	Brown	AKHS	Thompson
Jessica	Brown	BHS	Black
Tommy	Lee	VHS	Quintano
Aziz	Ansari	AKHS	Thompson
Michael	Scott	VHS	Quintano
Howie	Pierson	BHS	Busey
Lila	Thurmond	THS	Thurmond

Thanks, in advance, for any thoughts!

 

[Peter_SSs]

Welcome to the MrExcel board!

Would you be open to altering the Counsellor table a little as shown below?
If so, I have provided 2 options. They are basically the same but IF you have the LET function the second one is a little shorter to write.
I have assumed that the last cell in your shown desired outcome is a mistake.

merlinCLT.xlsm
	A	B	C	D	E
1	Student_First	Student_Last	High_School	Assigned_Counselor
2	Adam	Brown	AKHS	Thompson	Thompson
3	Jessica	Brown	BHS	Black	Black
4	Tommy	Lee	VHS	Quintano	Quintano
5	Aziz	Ansari	AKHS	Thompson	Thompson
6	Michael	Scott	VHS	Quintano	Quintano
7	Howie	Pierson	BHS	Busey	Busey
8	Lila	Thurmond	THS	Williams	Williams
9
10	Counselor_First	Counselor_Last	High_School	Caseload
11	Kelly	Thompson	AKHS	A-Moon
12	Mary	Black	BHS	A-Kit
13	Gary	Busey	BHS	Kita-ZZ
14	Bud	Shiffman	AKHS	Moona-ZZ
15	Scott	Williams	THS	A-ZZ
16	Susie	Quintano	VHS	A-ZZ
Sheet1

Cell Formulas
Range	Formula
D2:D8	D2	=FILTER(B$11:B$16,(C$11:C$16=C2)*(B2>=LEFT(D$11:D$16,FIND("-",D$11:D$16)-1))*(B2<=REPLACE(D$11:D$16,1,FIND("-",D$11:D$16),"")),"?")
E2:E8	E2	=LET(D,D$11:D$16,FILTER(B$11:B$16,(C$11:C$16=C2)*(B2>=LEFT(D,FIND("-",D)-1))*(B2<=REPLACE(D,1,FIND("-",D),"")),"?"))

 

[merlinCLT]

Peter,

Thanks for the quick response. It appears that your recommendation should work; however, I do not yet have access to the Let and Filter functions as my Excel account is managed as an enterprise account. I have reached out to our IT team to see whether it is possible to update in order to have access to the functions. In the meantime, I would welcome any additional suggestions for you or the community.

Thanks, again,

Andrew

 

[Fluff]

How about

=INDEX($B$11:$B$16,AGGREGATE(15,6,(ROW($B$11:$B$16)-ROW($B$11)+1)/($C$11:$C$16=C2)/(B2>=LEFT($D$11:$D$16,FIND("-",$D$11:$D$16)-1))/(B2<=REPLACE(D$11:D$16,1,FIND("-",D$11:D$16),"")),1))

 

[Eric W]

This works on your example, and I believe it should work overall, although you'd want to test it more. It requires that you sort the Counselor table by high school, then by caseload.

Book1
	A	B	C	D
1	Student_First	Student_Last	High_School	Assigned_Counselor
2	Adam	Brown	AKHS	Thompson
3	Jessica	Brown	BHS	Black
4	Tommy	Lee	VHS	Quintano
5	Aziz	Ansari	AKHS	Thompson
6	Michael	Scott	VHS	Quintano
7	Howie	Pierson	BHS	Busey
8	Lila	Thurmond	THS	Williams
9
10	Counselor Data
11
12	Counselor_First	Counselor_Last	High_School	Caseload
13	Kelly	Thompson	AKHS	A-Moon
14	Bud	Shiffman	AKHS	Moona-Z
15	Mary	Black	BHS	A-Kit
16	Gary	Busey	BHS	Kita-Z
17	Scott	Williams	THS	A-ZZ
18	Susie	Quintano	VHS	A-ZZ
Sheet10

Cell Formulas
Range	Formula
D2:D8	D2	=INDEX($B$13:$B$18,MATCH(C2&"|"&B2,$C$13:$C$18&"|"&LEFT($D$13:$D$18,FIND("-",$D$13:$D$18)-1)))
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[merlinCLT]

All,

Thank you for your contributions toward this inquiry! I was able to return the results to my question using both @Fluff and @Eric W recommended formulas. I was hoping that these formulas would enable me to accurately match students with their counselors; however, there is an additional component that I did not consider: Grade_level. Some counselors are assigned to a range of last names for specific grade levels, while others may be responsible for an entire grade level. This adds a layer of complexity that I am not certain how to address. I hold the same goal as originally expressed (to match the counselor to the student); however, I need to match the counselor based on the student last name and grade to the appropriate counselor assigned for that grade and name range. I can manipulate the data in anyway that will make it more feasible (i.e. I could partition out names or concatenate ranges, etc.).

The dataset I am using can be accessed here:

Please let me know if you have any recommendations for this additional layer of complexity.

Thanks to all!

 

[Fluff]

I have removed the link to your file as it looks to contain genuine names & email address.
Can you please supply another version with fake details. Thanks
Also on rows 19 & 20 you have 2 counsellors for that school one with an X for grade 10 & one for grade 11.
Does the X signify that counsellor handles the entire grade? If so who handles grades 9 & 12?

 

[Fluff]

If you change the G1:J1 headers as shown

Match Student with Counselor.xlsx
	A	B	C	D	E	F	G	H	I	J	K
1	SCHOOL	COUNSELOR FIRST NAME	COUNSELOR LAST NAME	EMAIL	BEGINNING ALPHA	END ALPHA	9	10	11	12	GRADE ALL
2	302 - Ardrey Kell	HYZZB	FYPCAUCLU	HYZZB.FYPCAUCLU@abc.xyz	A	BROOKS					X
3	302 - Ardrey Kell	ZUKL	FLRLYKCU	ZUKL.FLRLYKCU@abc.xyz	BROWN	DER					X
4	302 - Ardrey Kell	HKLOALZBC	KPSUO	HKLOALZBC.KPSUO@abc.xyz	DES	GONZALEZ					X
5	302 - Ardrey Kell	SPKB	OPKAGYZZ	SPKB.OPKAGYZZ@abc.xyz	GOOD	JOR					X
6	302 - Ardrey Kell	VEPOLAB	MKUUHO	VEPOLAB.MKUUHO@abc.xyz	JOS	MAR					X
7	302 - Ardrey Kell	VEPF	VPMZY	VEPF.VPMZY@abc.xyz	MAS	PATEL					X
8	302 - Ardrey Kell	EYPAEYK	OVELXXSPC	EYPAEYK.OVELXXSPC@abc.xyz	PATH	SAB					X
9	302 - Ardrey Kell	SPKLP	SVVUWKA	SPKLP.SVVUWKA@abc.xyz	SAC	THOMAS					X
10	302 - Ardrey Kell	ZPOPCFKP	KUMLCOUC	ZPOPCFKP.KUMLCOUC@abc.xyz	THOMPSON	ZZ					X
11	312 - Hough	QPNPWJEC	OWMYK	QPNPWJEC.OWMYK@abc.xyz	A	CA					X
12	312 - Hough	FPLCP	GUWZZPKF	FPLCP.GUWZZPKF@abc.xyz	CB	FE					X
13	312 - Hough	FPCP	VPAVERUZY	FPCP.VPAVERUZY@abc.xyz	FG	H					X
14	312 - Hough	VPZZLY	OSLAE	VPZZLY.OSLAE@abc.xyz	I	MA					X
15	312 - Hough	PZLOUC	JKPNYO	PZLOUC.JKPNYO@abc.xyz	MB	PE					X
16	312 - Hough	SLVEPYZ	VUMM	SLVEPYZ.VUMM@abc.xyz	PF	SM					X
17	312 - Hough	SPJEYC	KWMMLCU	SPJEYC.KWMMLCU@abc.xyz	SN	ZZ					X
18	334 - Cato	HYLAE	OSLAE	HYLAE.OSLAE@abc.xyz	A	ZZ					X
19	341 - Cochrane Collegiate Academy	FUCAÉ	EPKKLO	FUCAÉ.EPKKLO@abc.xyz	A	ZZ			X
20	341 - Cochrane Collegiate Academy	HYBP	FWCC	HYBP.FWCC@abc.xyz	A	ZZ		X
21	361 - Butler	VUKACYB	EYCFYKOUC	VUKACYB.EYCFYKOUC@abc.xyz	A	ZZ			X
22	361 - Butler	YZZYC	SYCCLAA	YZZYC.SYCCLAA@abc.xyz	A	L				X
23	361 - Butler	QPCCYZZY	YCJZLOE	QPCCYZZY.YCJZLOE@abc.xyz	A	L	X
24	361 - Butler	ZYO	MKUUHO	ZYO.MKUUHO@abc.xyz	A	ZZ		X
25	361 - Butler	SYZUFB	VPKK	SYZUFB.VPKK@abc.xyz	M	ZZ	X
26	361 - Butler	NPZYCALCP	UKAYJP	NPZYCALCP.UKAYJP@abc.xyz	M	ZZ				X
27	377 - East Meck	QUCPAEPC	GLZZLPSO	QUCPAEPC.GLZZLPSO@abc.xyz	A	EI		X	X	X
Counselors

Match Student with Counselor.xlsx
	A	B	C	D	E	F
1	Student_ID#	Student_First_Name	Student_Last_Name	High_School	Grade	Counselor_Email
2	22151574	Max	Blackmore	302 - Ardrey Kell	9	HYZZB.FYPCAUCLU@abc.xyz
3	71844409	Manveer	Hensley	302 - Ardrey Kell	9	SPKB.OPKAGYZZ@abc.xyz
4	20601795	Kieren	Livingston	302 - Ardrey Kell	9	VEPOLAB.MKUUHO@abc.xyz
5	26801857	Kacy	Palacios	302 - Ardrey Kell	9	VEPF.VPMZY@abc.xyz
6	34235789	Jaydn	Bowes	302 - Ardrey Kell	10	HYZZB.FYPCAUCLU@abc.xyz
7	72332711	Marni	Chavez	302 - Ardrey Kell	10	ZUKL.FLRLYKCU@abc.xyz
8	28380377	Amani	Edmonds	302 - Ardrey Kell	10	HKLOALZBC.KPSUO@abc.xyz
9	38567646	Evelyn	Henson	302 - Ardrey Kell	10	SPKB.OPKAGYZZ@abc.xyz
137	17510804	Marsha	Macdonald	334 - Cato	12	HYLAE.OSLAE@abc.xyz
138	58147960	Dale	Reeves	341 - Cochrane Collegiate Academy	9	#NUM!
139	12693285	Aden	Bains	341 - Cochrane Collegiate Academy	10	HYBP.FWCC@abc.xyz
140	34406229	Bradley	Broadhurst	341 - Cochrane Collegiate Academy	10	HYBP.FWCC@abc.xyz
141	91233759	Ellie	Carpenter	341 - Cochrane Collegiate Academy	10	HYBP.FWCC@abc.xyz
142	61275464	Alyssa	Molloy	341 - Cochrane Collegiate Academy	10	HYBP.FWCC@abc.xyz
143	72457337	Alfie-Lee	Walmsley	341 - Cochrane Collegiate Academy	10	HYBP.FWCC@abc.xyz
144	18911914	Milton	Delgado	341 - Cochrane Collegiate Academy	11	FUCAÉ.EPKKLO@abc.xyz
145	52547398	Fox	Hayes	341 - Cochrane Collegiate Academy	11	FUCAÉ.EPKKLO@abc.xyz
155	29660705	Aleksandra	Zuniga	341 - Cochrane Collegiate Academy	11	FUCAÉ.EPKKLO@abc.xyz
156	6898147	Ishmael	Burch	341 - Cochrane Collegiate Academy	12	#NUM!
Students

Cell Formulas
Range	Formula
F2:F9,F137:F145,F155:F156	F2	=INDEX(Counselors!$D$2:$D$138,AGGREGATE(15,6,(ROW(Counselors!$D$2:$D$138)-ROW(Counselors!$D$2)+1)/(Counselors!$A$2:$A$138=D2)/IF(Counselors!$K$2:$K$138="X",(C2>=Counselors!$E$2:$E$138)/(C2<=Counselors!$F$2:$F$138&"Z"),(Counselors!$G$1:$I$1=E2)/(Counselors!$G$2:$I$138="X")),1))

 

[merlinCLT]

Fluff,

Thanks for the clarification. The document (Match Student with Counselor) now only reflect fictitious data.

I cleaned rows 19&20 so each grade would be represented. As you can see in the data, there exists much variability between school counselor caseload assignments across schools. The 'X' indicates that that counselor handles that grade for the name range expressed in columns E&F on the Counselors worksheet.

Let me know if any additional clarification is needed.

Thanks,

Andrew

 

[Fluff]

Thanks for that,, a slight change to the formula in post#8 it should be

=INDEX(Counselors!$D$2:$D$138,AGGREGATE(15,6,(ROW(Counselors!$D$2:$D$138)-ROW(Counselors!$D$2)+1)/(Counselors!$A$2:$A$138=D2)/IF(Counselors!$K$2:$K$138="X",(C2>=Counselors!$E$2:$E$138)/(C2<=Counselors!$F$2:$F$138&"Z"),(Counselors!$G$1:$J$1=E2)/(Counselors!$G$2:$J$138="X")),1))