lookup formula not picking up correct data

[skeyesq]

I using Excel 2003 to create a "Lookup" formula to calculate grade points and
grade point averages. I want to insert the grade and have the grade
converted to a grade point then multiply by the number of credits for the
class. I created a vector with the grade and a corresponding vector with the
grade point. The formula I am using is =LOOKUP(D6, B1:N1, B2:N2) where D6 is
the lookup value (the grade); b1:n1 is the lookup vector; and b2:n2 is the
result vector. The result is that I get an "#N/A" error message when I input
some grades, I get the incorrect grade point when I input other grades, and
correct grade points when I input other grades. (Ex. 1. an "A" gets an
"#N/A" error message. Ex. 2. an "A+" is calculated at 3.67 instead of 4 as
indicated in the corresponding result vector. Ex. 3. a "C" is caluclated at
2.67 instead of 2 as indicated in the corresponding result vector. Ex. 4. A
"B" is calculated at 3.0 as indicated in the corresponding result vector.)
What am I doing incorrectly?

 

[pdberger]

Skeyesq --

I see a couple of problems. First, the second argument in your lookup
formula should be range that has all columns, not just the reference colomn.
Second, you need to have the 'false' as the fourth argument. Here's
something that works for me:

A B
1 A+ 4.00
2 A 3.67
3 A- 3.50
4 B+ 3.33
5 B 3.25
6 B- 3.00
7
8 A+ =VLOOKUP(A8,A1:B6,2,FALSE)

HTH

 

[Gord Dibben]

Is your lookup vector sorted in ascending order as required?


Gord Dibben MS Excel MVP