Problem with VLOOKUP working backwards

[DeezNuts]

I am trying to make a grade scale and for whatever reason it is working wrong. If any number under 100 is tried it gives a N/A and anything of 100 gives a F not how it should work. What am doing wrong with this?


Excel 2013

	A	B	C	D	E
1	22.00%			#N/A
2
3			100.00%	A
4			95.00%	A
5			94.00%	A-
6			90.00%	A-
7			89.00%	B+
8			87.00%	B+
9			84.00%	B
10			83.00%	B
11			82.00%	B-
12			80.00%	B-
13			79.00%	C+
14			77.00%	C+
15			76.00%	C
16			70.00%	C
17			69.00%	D
18			60.00%	D
19			59.00%	F
20			0.00%	F

<colgroup><col style="width: 25pxpx"><col><col><col><col><col></colgroup><thead>
</thead><tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula D1 =VLOOKUP(A1,$C$3:$D$20,2) <thead> </thead><tbody> </tbody>

<tbody>
</tbody>

 

[Momentman]

VLOOKUP with approximate match requires sorting the data in ascending order. See below(and 2 formula examples)

Excel 2010
	A	B	C	D
1	70.50%		C	C
2
3		0%	F
4		59%	F
5		60%	D
6		69%	D
7		70%	C
8		76%	C
9		77%	C+
10		79%	C+
11		80%	B-
12		82%	B-
13		83%	B
14		84%	B
15		87%	B+
16		89%	B+
17		90%	A-
18		94%	A-
19		95%	A
20		100%	A
Sheet13

Cell Formulas
Range	Formula
C1	=INDEX($C$3:$C$20,MATCH(A1,$B$3:$B$20,1))
D1	=VLOOKUP(A1,$B$3:$C$20,2)

 

[Marcelo Branco]

Maybe...

	A​	B​	C​	D​
1​	22,00%​			F​
2​
3​			100,00%​	A​
4​			95,00%​	A​
5​			94,00%​	A-​
6​			90,00%​	A-​
7​			89,00%​	B+​
8​			87,00%​	B+​
9​			84,00%​	B​
10​			83,00%​	B​
11​			82,00%​	B-​
12​			80,00%​	B-​
13​			79,00%​	C+​
14​			77,00%​	C+​
15​			76,00%​	C​
16​			70,00%​	C​
17​			69,00%​	D​
18​			60,00%​	D​
19​			59,00%​	F​
20​			0,00%​	F​

Formula in D1
=INDEX(D3:D20,MATCH(A1,C3:C20,-1))

Hope this helps

M.

 

[DeezNuts]

I ended up resorting them smallest to largest and used =LOOKUP(A1,C3:C20,D3:D20) thank you both for your help

 

[Momentman]

Just curious, what should be the outcome for say 69.5%? C or D