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?

 

[DataAnalyst]

I had this same problem and usually doing "text to columns" on the column with the value to be looked up takes care of it. This time it didn't.
After reading this thread I checked for leading or trailing spaces and found them in the list of reference values. This is a list I've used all year, copying and pasting to new workbooks, and never had this problem before.
I changed a few of the values by hand, but ended up copying the list into a new sheet and the problem went away. Interesting that I had not done anything to the first sheet I pasted. Somehow the leading and trailing spaces got there on their own.

 

[Aladin Akyurek]

I had this same problem and usually doing "text to columns" on the column with the value to be looked up takes care of it. This time it didn't.
After reading this thread I checked for leading or trailing spaces and found them in the list of reference values. This is a list I've used all year, copying and pasting to new workbooks, and never had this problem before.
I changed a few of the values by hand, but ended up copying the list into a new sheet and the problem went away. Interesting that I had not done anything to the first sheet I pasted. Somehow the leading and trailing spaces got there on their own.

There is no evidence as yet that there are gremlins in Excel...

 

[DataAnalyst]

There is no evidence as yet that there are gremlins in Excel...

Only anecdotal!

Which leads to the big question in my field (medical) how many anecdotes does it take to convince the doubters? How long before the authorities accept the obvious?

In software it's different... My boss' Excel doesn't work the same as my Excel... it's probably something in the way our computers are set up by our employer. Many variations, many opportunities for glitches and gremlins.

 

[MrFitExcel]

Had a similar problem, you need to make sure that the table you are looking up stays constant. instead of B22:C32 use $B$22:$C$32 before auto filling the rest of the cells.
you probably won't need to look back but this is more to help anyone like me who comes looking to solve this problem in the future.