Vlookup

[youngrick]

Hello all,

I have a question about using the VLOOKUP tool. I am using it correctly as far as placing everything in the right position when creating the formula, but my problem comes in after I set up the formula.

Ex. I am trying to turn letter grades in to numbers:

A=4.000, A-=3.667, B+=3.333....etc. When I create the table only some of the numbers show up with the grade they are supposed to. Others show a different number. Like B comes up as 3.667 instead of 3.000. How do I correct this issue?

Whoever can help it is much appreciated!

 

[number kruncher]

Are you using the optional test as FALSE e.g.

=VLOOKUP("A",B1:C4,2,FALSE)

Else if omitted, the result will be the closest match.

 

[SuperFerret]

Is your last bit set to FALSE?

=VLOOKUP(A2,A:B,2,FALSE)

This will look only for an exact match

 

[T. Valko]

Hello all,

I have a question about using the VLOOKUP tool. I am using it correctly as far as placing everything in the right position when creating the formula, but my problem comes in after I set up the formula.

Ex. I am trying to turn letter grades in to numbers:

A=4.000, A-=3.667, B+=3.333....etc. When I create the table only some of the numbers show up with the grade they are supposed to. Others show a different number. Like B comes up as 3.667 instead of 3.000. How do I correct this issue?

Whoever can help it is much appreciated!

Show us the ENTIRE grade range.

 

[tony0682]

Can you show the exact formula you're using? Make sure you're using FALSE for range lookup and your ranges are static by adding the $ to the cell address.

 

[j_latendre]

You could try this long IF statement :S

=IF(A1="A",4,IF(A1="A-",3.667,IF(A1="B+",3.333,IF(A1="B",3,IF(A1="B-",2.667,IF(A1="C+",2.333,IF(A1="C",2,IF(A1="C-",1.667,IF(A1="D+",1.333,IF(A1="D",1,IF(A1="D-",0.667,"")))))))))))

Jesse

 

[Aladin Akyurek]

Try something like...

=VLOOKUP(LetterGrade,{"A",4.0;"A-",3.667;"B+",3.333;...},2,0)

 

[youngrick]

Thanks guys!

All that I was missing was the FALSE for exact number.

 

[devthomp]

Hello all,

I have a question about using the VLOOKUP tool. I am using it correctly as far as placing everything in the right position when creating the formula, but my problem comes in after I set up the formula.

Ex. I am trying to turn letter grades in to numbers:

A=4.000, A-=3.667, B+=3.333....etc. When I create the table only some of the numbers show up with the grade they are supposed to. Others show a different number. Like B comes up as 3.667 instead of 3.000. How do I correct this issue?

Whoever can help it is much appreciated!

For your purposes you could use nested IFs:

=IF(A33<=1, "F", IF(A33<=2, "D", ...

Remember to close out your parentheses, and remember to start from the bottom (I'm really not sure if you can use AND or OR in these nested-ifs).
This uses the basic sytax of the IF function:

IF( condition1, value_if_true1, IF( condition2, value_if_true2, value_if_false2 ))

Where the value_if_false is just another IF function.