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!

 

[merlinCLT]

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))

Thanks, Fluff! I think this should do the trick! I will look forward to giving it a go tomorrow.

Appreciate everyone's contributions!

 

[Eric W]

My original formula really couldn't be adapted to the new requirements, but this should work:

Book1
	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	#N/A
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
Students

Cell Formulas
Range	Formula
F137:F145,F2:F9	F2	=INDEX(Counselors!$D$2:$D$30,MATCH(4,(D2=Counselors!$A$2:$A$30)+(C2>=Counselors!$E$2:$E$30)+(C2<=Counselors!$F$2:$F$30&"ZZ")+(Counselors!$K$2:$K$30="X")+(OFFSET(Counselors!$G$2:$G$30,0,E2-9)="X"),0))
Press CTRL+SHIFT+ENTER to enter array formulas.