VLOOKUP - "Value not Available Error...

[Webmonkey78]

I have a list that looks up a Grade letter for a GPA. I have 5 Classes, each class shows a grade (by letter) in one Cell and in another Cell I have a VLOOKUP that gets the GPA based on the Letter Grade. However, with the 5th VLOOKUP I get a Value of #N/A instead of the GPA, which I get returned for the other 4.

Here is the VLOOKUP formula's:
=VLOOKUP(F6,B22:C32,2,TRUE)
=VLOOKUP(F7,B22:C32,2,TRUE)
=VLOOKUP(F8,B22:C32,2,TRUE)
=VLOOKUP(F9,B22:C32,2,TRUE)
=VLOOKUP(F10,B22:C32,2,TRUE)

The lookup field Goes to the following, Starting cell has the Value A-, end cell is 0:
Grade Credit
A- 3.7
A- 4
B- 2.7
B 3
B+ 3.3
C- 1.7
C 2
C+ 2.3
D 1
D+ 1.3
F 0

Here's what the GPA cells have

Class Grade Percentage GPA
Class Grade Percentage GPA
Biology B 86.92 4
English C+ 79.71 2.3
Geometry B- 81.79 2.7
French D- 64.93 1
World History A- 95.38 #N/A

At first I thought it might be because of the negative, but it couldn't be because there is a B- showing. So I changed the grade to Show an A and it still shows the #N/A. Any thoughts?

 

[Jonmo1]

Welcome to the board...

Try changing TRUE to FALSE


Hope that helps.

 

[Webmonkey78]

Nope. Thanks though.

Not sure if it has anything to do with it being the last one in the list or not, I wouldn't it is.

Also, I changed the sorting order a few different times and that did not help either.

::Update::
Odd... I just tried to see if it has anything to do with it being the last one and it doesn't but I did just notice that if I change the Grade to anything other than an A it works fine. If I change it to a B it gives a GPA of 4. Odd.... very strange.

 

[Jonmo1]

Well it's working for me to an extent...

1. You have A- listed twice in B22 and B23
2. You do not have D- Listed.

Excel Workbook
	E	F	G	H
4	Class	Grade	Percentage	GPA
5	Class	Grade	Percentage	GPA
6	Biology	B	86.92	3
7	English	C+	79.71	2.3
8	Geometry	B-	81.79	2.7
9	French	D-	64.93	#N/A
10	World History	A-	95.38	3.7
Sheet2

Excel Workbook
	B	C
22	A-	3.7
23	A-	4
24	B-	2.7
25	B	3
26	B+	3.3
27	C-	1.7
28	C	2
29	C+	2.3
30	D	1
31	D+	1.3
32	F	0
Sheet2

 

[ADeYoung]

I re-created this in a spreadsheet and it works fine. I would recommend that you check your formula in cell H10 to make sure the VLOOKUP range is correct. Make sure it is looking to B22:C32

 

[Webmonkey78]

I created the sheet again and still the same issue. even when I use False. I just cannot figure out why.

 

[Aladin Akyurek]

You have A- twice. Is that correct?

 

[scottylad2]

The #NA error usually refers to the item you'r looking up not being in the lookup table, try checking the spellings correct in the lookup value and the table and also check there's no leading or trailing spaces.

 

[Webmonkey78]

No, I no Longer have A- twice.

I have the following table I am looking at.

Grade	Credit
A  	4
A+	4.1
A- 	3.7
B	3
B+	3.3
B-  	2.7
C	2
C+	2.3
C- 	1.7
D  	1
D+	1.3
F 	0

Since I have updated the look up table as shown above, It works with A+ but not A or A-.

I've also checked for additional spaces and extra characters, so I do not believe that is the case.


:::UPDATE:::
Ok, I've gotten it fixed. Some how after I checked for Spacing, I went back and looked again and there was an additional space I didn't catch or added somehow. So it is working now. Just keep in mind I did create the spreadsheet again to get it to work. I do not know why it didn't work with the earlier version with the spaces removed. Possibly due to formatting or another issue.

Thanks again for your input and help.

 

[scottylad2]

Glad you got it sorted. It may well that the formatting carries extra spaces