Odd problem with VLOOKUP or LOOKUP

[DeezNuts]

I am trying to convert letter grades to GPA. I have looked at all the sites on the first 2 pages of Google before I came here to make a fool of myself again.

If I use VLOOKUP with the lists in a certain order I get the wrong results for B+ says 2.7 which it should be 3.3 If I reorder the list and use the same formula the B+ will be 3.3 but the A no longer shows 4.0 instead it shows a N/A

I can always use

VLOOKUP(A23,{"A",4;"A-",3.7;"B+",3.3;"B",3;"B-",2.7;"C+",2.3;"C",2;"D",1;"F",0},2,FALSE)

Just curious what am doing wrong with both VLOOKUP or LOOKUP




Excel 2013

	A	B	C	D	E	F	G	H
1	B+	2.7			B+	2.7
2			A	4			A	4
3			A-	3.7			A-	3.7
4			B+	3.3			B+	3.3
5			B	3			B	3
6			B-	2.7			B-	2.7
7			C+	2.3			C+	2.3
8			C	2			C	2
9			D	1			D	1
10			F	0			F	0
11
12	A	#N/A			A	#N/A
13			F	0			F	0
14			D	1			D	1
15			C	2			C	2
16			C+	2.3			C+	2.3
17			B-	2.7			B-	2.7
18			B	3			B	3
19			B+	3.3			B+	3.3
20			A-	3.7			A-	3.7
21			A	4			A	4
22
23	B+	3.3

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell Formula B1 =LOOKUP(A1,C2:D10) F1 =VLOOKUP(E1,G2:H10,2) B12 =LOOKUP(A12,C13:D21) F12 =VLOOKUP(E12,G13:H21,2) B23 =VLOOKUP(A23,{"A",4;"A-",3.7;"B+",3.3;"B",3;"B-",2.7;"C+",2.3;"C",2;"D",1;"F",0},2,FALSE) <tbody> </tbody>

<tbody>
</tbody>

EDIT*

No sooner do I post this but I see the error of my ways I tried

=VLOOKUP(E1,G2:H10,2,FALSE)

and it works perfectly. No clue what the False does but it works

 

[JoeMo]

VLOOKUP with the fourth argument omitted defaults that argument to TRUE. Under those conditions the lookup values in your table must be in ascending order. B,B-,B+ is ascending order. Since your lookup table is not in ascending order, the formula returns the wrong value. You can set the fourth argument to FALSE to require an exact match that does not require the lookup table to be in any particular order.

For example in F1 enter:
=VLOOKUP(E1,G2:H10,2,FALSE)

 

[DeezNuts]

Thank you for explaining the False part JoeMo wasnt grasping that part. So if it is truly ascending no need for the true statement its assumed by excel but if I want the perfect side by side I have to add the false if its not ascending.