Return a Value Based on a Range Criteria AND a String Match Criteria

[blast0]

I have scoured the internet and I cannot find anyone trying to do specifically what I am doing in Excel, so hopefully you can help.

Consider the following table (Range"A1:D16"):

College <tbody> </tbody>	Score Range Min <tbody> </tbody>	Score Range Max <tbody> </tbody>	Letter Grade <tbody> </tbody> <tbody> </tbody>
Rutgers <tbody> </tbody>	0 <tbody> </tbody>	60 <tbody> </tbody>	F <tbody> </tbody>
Rutgers <tbody> </tbody>	61 <tbody> </tbody>	70 <tbody> </tbody>	D <tbody> </tbody>
Rutgers <tbody> </tbody>	71 <tbody> </tbody>	80 <tbody> </tbody>	C <tbody> </tbody>
Rutgers <tbody> </tbody>	81 <tbody> </tbody>	90 <tbody> </tbody>	B <tbody> </tbody>
Rutgers <tbody> </tbody>	91 <tbody> </tbody>	100 <tbody> </tbody>	A <tbody> </tbody>
Princeton <tbody> </tbody> <tbody> </tbody>	0 <tbody> </tbody>	80 <tbody> </tbody>	F <tbody> </tbody>
Princeton <tbody> </tbody> <tbody> </tbody>	81 <tbody> </tbody>	85 <tbody> </tbody>	D <tbody> </tbody>
Princeton <tbody> </tbody> <tbody> </tbody>	86 <tbody> </tbody>	90 <tbody> </tbody>	C <tbody> </tbody>
Princeton <tbody> </tbody> <tbody> </tbody>	91 <tbody> </tbody>	95 <tbody> </tbody>	B <tbody> </tbody>
Princeton <tbody> </tbody> <tbody> </tbody>	96 <tbody> </tbody>	100 <tbody> </tbody>	A <tbody> </tbody>
Monmouth <tbody> </tbody> <tbody> </tbody>	0 <tbody> </tbody>	40 <tbody> </tbody>	F <tbody> </tbody>
Monmouth <tbody> </tbody> <tbody> </tbody>	41 <tbody> </tbody>	55 <tbody> </tbody>	D <tbody> </tbody>
Monmouth <tbody> </tbody> <tbody> </tbody>	56 <tbody> </tbody>	70 <tbody> </tbody>	C <tbody> </tbody>
Monmouth <tbody> </tbody> <tbody> </tbody>	71 <tbody> </tbody>	85 <tbody> </tbody>	B <tbody> </tbody>
Monmouth <tbody> </tbody> <tbody> </tbody>	86 <tbody> </tbody>	100 <tbody> </tbody>	A <tbody> </tbody>

<tbody>
</tbody>

I want to be able to input a College into column "F", input a Score into column "G", and have an excel formula in Column "H" tell me the letter grade.

My expected results are as follows(Range"F1:H4"):

Score <tbody> </tbody>	College <tbody> </tbody>	Letter Grade <tbody> </tbody>
79	Rutgers	C
79	Princeton	F
79	Monmouth	B

<tbody>
</tbody>

 

[blast0]

So I have come up with the following solution, please consider for cell H2:
{=IFERROR(INDEX(D2:D16,MATCH(1,(A2:A16=F2)*(B2:B16<=G2)*(C2:C16>=G2),0)),"")}

The problem I think I am having now is that in the real world I have a thousands of rows of schools and grade ranges, and I have thousands of rows of test data to validate, and Excel is not responding. Any help would be greatly appreciated.

 

[Fluff]

Not sure if it will be any better, but a non array formula

Excel 2013/2016
	A	B	C	D	E	F	G	H
1	College	Score Range Min	Score Range Max	Letter Grade		Score	College	Letter Grade
2	Rutgers	0	60	F		79	Rutgers	C
3	Rutgers	61	70	D		79	Princeton	F
4	Rutgers	71	80	C		79	Monmouth	B
5	Rutgers	81	90	B
6	Rutgers	91	100	A
7	Princeton	0	80	F
8	Princeton	81	85	D
9	Princeton	86	90	C
10	Princeton	91	95	B
11	Princeton	96	100	A
12	Monmouth	0	40	F
13	Monmouth	41	55	D
14	Monmouth	56	70	C
15	Monmouth	71	85	B
16	Monmouth	86	100	A
Data

Cell Formulas
Range	Formula
H2	=IFERROR(INDEX($D$2:$D$16,AGGREGATE(15,6,(ROW($D$2:$D$16)-ROW($D$2)+1)/(($A$2:$A$16=G2)*($B$2:$B$16<=F2)*($C$2:$C$16>=F2)),1)),"")

 

[pgc01]

Hi
Welcome to the board

Another option, in H2:

=LOOKUP(F2,$B$2:$B$16/($A$2:$A$16=G2),$D$2:$D$16)

I assumed that, like in your example, for each college the Range Min's are in ascending order.
I don't need the Range Max's.