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

[TABLE="width: 500"]
<tbody>[TR]
[TD]

<tbody>
[TD="class: xl65"]College[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109"]Score Range Min[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65"]Score Range Max[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65"][TABLE="width: 44"]
<tbody>[TR]
[TD="width: 44"]Letter Grade[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"]Rutgers[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]0[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115, align: right"]60[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"]Rutgers[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]61[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115, align: right"]70[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]D[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]Rutgers[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]71[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115, align: right"]80[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65"]C[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"]Rutgers[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]81[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]90[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"]Rutgers[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]91[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115, align: right"]100[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65"]A[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]

<tbody>
[TD="class: xl65"]Princeton[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]80[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65"]F[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"]

<tbody>
[TD="class: xl65"]Princeton[/TD]

</tbody>

[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]81[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]85[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]D[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"]

<tbody>
[TD="class: xl65"]Princeton[/TD]

</tbody>

[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]86[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115, align: right"]90[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65"]C[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]Princeton[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]91[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115, align: right"]95[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]Princeton[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]96[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]100[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65"]A[/TD]

</tbody>

[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]

<tbody>
[TD="class: xl65"]Monmouth[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]0[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]40[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]F[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]Monmouth[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]41[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115, align: right"]55[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]D[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"]

<tbody>
[TD="class: xl65"]Monmouth[/TD]

</tbody>

[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]56[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115, align: right"]70[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]C[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]

<tbody>
[TD="class: xl65"][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]Monmouth[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 109"]
<tbody>[TR]
[TD="class: xl65, width: 109, align: right"]71[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 115"]
<tbody>[TR]
[TD="class: xl65, width: 115, align: right"]85[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]B[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="class: xl65, width: 76"]

<tbody>
[TD="class: xl65"]Monmouth[/TD]

</tbody>

[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]86[/TD]

</tbody>

[/TD]
[TD]

<tbody>
[TD="class: xl65, align: right"]100[/TD]

</tbody>

[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="class: xl65, width: 44"]A[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

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

[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 41"]
<tbody>[TR]
[TD="width: 41"]Score[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 76"]
<tbody>[TR]
[TD="width: 76"]College[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 44"]
<tbody>[TR]
[TD="width: 44"]Letter Grade[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD]Rutgers[/TD]
[TD]C[/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD]Princeton[/TD]
[TD]F[/TD]
[/TR]
[TR]
[TD]79[/TD]
[TD]Monmouth[/TD]
[TD]B[/TD]
[/TR]
</tbody>[/TABLE]

 

[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.