Convert Numerical Grade to Letter Grade

[meppwc]

I am asking for assistance with a formula that is related to school grades that are both numerical and letter grades.

In cell, A1 I will manually enter the numerical grade (Example: 84.25)
In cell B1 I need a formula that returns a letter grade from a table that corresponds with the numerical grade that I entered in cell A1 (Example: The formula returns the value of "B" because the entry of 84.25 is equal to to or falls between 83 and 86)

Cells C1:E12 represent a table that shows the letter grade and the numerical range that correlates to the letter grade.
Cells C1 through C12 are the letter grades (A, A-, B+, B, B-, C+, C, C-, D+, D, D-, F)
Cells D1 though D12 contain the numbers (94, 90, 87, 83, 80, 77, 73, 70, 67, 63, 60, 0)
Cells E1 through E12 contain the numbers (100, 93, 89, 86, 83, 79, 76, 72, 69, 66, 62, 59)

 

[AlanY]

it's so much easier if you can rearrange your table like this

Excel 2013/2016
	A	B	C	D	E
1	82.25	B-	F	0	59
2			D-	60	62
3			D	63	66
4			D+	67	69
5			C-	70	72
6			C	73	76
7			C+	77	79
8			B-	80	83
9			B	83	86
10			B+	87	89
11			A-	90	93
12			A	94	100
Sheet3

Cell Formulas
Range	Formula
B1	=LOOKUP(A1,D1:D12,C1:C12)

 

[Weazel]

maybe something like...

=LOOKUP(A1,{0;60;63;67;70;73;77;80;83;87;90;94},{"F";"D-";"D";"D+";"C-";"C";"C+";"B-";"B";"B+";"A-";"A"})

you could also sort the table ascending/smallest to largest on the values and do a lookup....either way

 

[Scott T]

If you do not want to rearrange the table you can use index match

Excel 2010
	A	B	C	D	E
1	84.25	B	A	94	100
2			A-	90	93
3			B+	87	89
4			B	83	86
5			B-	80	83
6			C+	77	79
7			C	73	76
8			C-	70	72
9			D+	67	69
10			D	63	66
11			D-	60	62
12			F	0	59
Sheet4

Cell Formulas
Range	Formula
B1	=INDEX(C1:C12,MATCH(A1,E1:E12,-1))

 

[meppwc]

Thank you all for the assistance.