Vlookup and certain "bugs"?

[kylera]

I'm trying to make a report card of sorts with Excel 2003. On one sheet
will be where I type everything up -- classes, grades, scores, etc. In
one column (in my case, B, starting with Row 4), I will type in a
letter grade (A+, A, A-, replace A with B~D and F) and the column next
to it (C) will display the corresponding GPA using VLOOKUP. The VLOOKUP
function connects this sheet to another sheet that's aptly called GPA.
In that sheet, column A has letter grades in descending order from A+
to F. The next column has its corresponding point (4.3, 4, 3.7, 3.3, 3,
2.7, etc). I have run into a couple of issues to which solutions to
either one or both will be greatly appreciated.

GPA sheet
A B
Grade | Point
A+ 4.3
A 4
A- 3.7
B+ 3.3
B 3
B- 2.7
so on and so forth
F 0

1. When I type in "A", the VLOOKUP cell next to it gives me a #N/A
error. When I type in anything other than B or F, they give a totally
different value (C is supposed to give 2 but gives 2.7 and D gives 1.7
instead of 1). Only B and F give proper values.

 

[Don Guillett]

Try re-typing your a+,a etc. (try the A first)

 

[Ron Rosenfeld]

I'm trying to make a report card of sorts with Excel 2003. On one sheet
will be where I type everything up -- classes, grades, scores, etc. In
one column (in my case, B, starting with Row 4), I will type in a
letter grade (A+, A, A-, replace A with B~D and F) and the column next
to it (C) will display the corresponding GPA using VLOOKUP. The VLOOKUP
function connects this sheet to another sheet that's aptly called GPA.
In that sheet, column A has letter grades in descending order from A+
to F. The next column has its corresponding point (4.3, 4, 3.7, 3.3, 3,
2.7, etc). I have run into a couple of issues to which solutions to
either one or both will be greatly appreciated.

GPA sheet
A B
Grade | Point
A+ 4.3
A 4
A- 3.7
B+ 3.3
B 3
B- 2.7
so on and so forth
F 0

1. When I type in "A", the VLOOKUP cell next to it gives me a #N/A
error. When I type in anything other than B or F, they give a totally
different value (C is supposed to give 2 but gives 2.7 and D gives 1.7
instead of 1). Only B and F give proper values.

It would have been useful if you had posted the formula you used. Saves me
from having to read minds.

My guess is that you did not specify range_lookup so it defaulted to True
which would require that your grades be sorted (which they are not). Given the
posting of your table, you probably shoud have this argument specified as
false.
--ron

 

[Gav123]

Hi Kylera,

I formatted the text for the grades on sheet GPA to General and the points
to number...

GPA sheet
A B
Grade | Point
A+ 4.3
A 4
A- 3.7
B+ 3.3
B 3
B- 2.7

On your reports card sheet in cell C4 enter this...

=VLOOKUP(B4,GPA!A4:B9,2,0)

Adjust ranges to suit GPA!A4:B9

This works fine on Excel 2003...

Hope this helps,

Gav.

 

[Don Guillett]

Also add ,false as the last parameter