Is vlookup the way to solve this challenge?

[jjamjatra]

I have this "scale" data in sheet2:

Grades Points

A 4

A- 3.7

B+ 3.3

B 3

B- 2.7

C+ 2.3

C 2

C- 1.7

D+ 1.3

D 1

D- 0.7

F 0

and I have rows like this in sheet1:

term grade points credits course core

2012D1 B- 4 SPN-110 01 Elementary Spanish I

2012D1 C+ 4 PHY-101 01 Physics for Non-Science Majors NW

2012D1 B 4 MTH-140 03 Integrated Precalc/Calc I


I want to use the scale data in sheet2 to lookup the value of the grade in sheet1 and assign proper points. Is vlookup the way to go? Thanks from this rookie on first post.

 

[dan_l]

Yes. Assuming you want to look up the letter grade and fet the points, a vlookup would do it. Although for the lazy, since the points are numeric, you could also get away with a sumif....

 

[sgmpatnaik]

@jjamjatra


Hi


is it possible to upload a sample work book


Thanks


SP

 

[jjamjatra]

Thanks, I am getting back to this now and stuck on an "Invalid cell reference error" with the following formula in Sheet1!$C$3


=VLOOKUP([grade],ScaleTable[Grades],ScaleTable[Points],FALSE)


I click on the helper wizard and choose "Show calculation steps..".

Clicking Evaluate causes this step-wise evaluation:


VLOOKUP("B",ScaleTable[Grades],ScaleTable[Points],FALSE)

then

VLOOKUP("B",Sheet2!$A$2:$A$13,ScaleTable[Points],FALSE)

then

VLOOKUP("B",Sheet2!$A$2:$A$13,Sheet2!$B$2:$B$13,FALSE)


at this point Excel tells me the next calculation will result in an error.

When I click Evaluate, it leaves me with the #REF! but this looks correct to me so I feel stuck.


Here is another formula that did not fail but did not work correctly either in Sheet1!$C$9


VLOOKUP("C+",ScaleTable[Grades],ScaleTable[Points],FALSE)

then

VLOOKUP("C+",Sheet2!$A$2:$A$13,ScaleTable[Points],FALSE)

then

VLOOKUP("C+",Sheet2!$A$2:$A$13,Sheet2!$B$2:$B$13,FALSE)

result is C+ but should be 2.3 which is the value in the ScaleTable for a C+


And finally,another row with the same grade results in #value! error. Here we are in Sheet1!$C$12


VLOOKUP("C+",ScaleTable[Grades],ScaleTable[Points],FALSE)

then

VLOOKUP("C+",Sheet2!$A$2:$A$13,ScaleTable[Points],FALSE)

then

VLOOKUP("C+",Sheet2!$A$2:$A$13,Sheet2!$B$2:$B$13,FALSE)

result this time is a #VALUE! error.


This seems like it should work. Any idea on what is wrong?


How do I upload a sample workbook to this forum/thread ?

 

[kaushik03]

jjamjatra,


I believe the problem is with your third argument of vlookup formula which should be column index number


However,Plz upload the file here:

http://chandoo.org/forums/topic/posting-a-sample-workbook


Kaushik

 

[jjamjatra]

Thanks for your ideas. Hopefully, I uploaded this properly and you might be able to have a look:


https://docs.google.com/open?id=0BwA1QXWV-S3AakQ1cGJ6TG12V0k


I simply changed the formula to read:


=VLOOKUP([grade],ScaleTable[Grades],2,FALSE)


instead of the previous attempt which you thought might be wrong:


=VLOOKUP([grade],ScaleTable[Grades],ScaleTable[Points],FALSE)


Thanks for your continued time on this.

 

[Debraj]

Hi jjamjatra,


Vlookup's Syntax

vlookup(this value, in this list, and get me value in this column, [is-my-list-sorted?])


visit posting a sample workbook for uploading sample workbook..


as You are providing all references from a single Table ScaleTable.. and you are giving references always from same table to same table..

I guess You need [Grades] from Sheet 1 and need to lookup in ScaleTable(Sheet2!A1:B13)


So (I guess) try in Sheet1!C2

=VLOOKUP(Sheet1!B2,ScaleTable,2,False) and drag below..


and please provide permission to all also for your sample workbook..

Regards,

Deb

 

[jjamjatra]

One workbook with 2 sheets (sheet1 and sheet2).

Sheet1 has table called GradeTable.

[pre]

term	grade	points	credits	course	core
2012D1	D-	#REF!	4	SPN-110 01 Elementary Spanish I
2012D1	C	#REF!	4	PHY-101 01 Physics for Non-Science Majors	NW
2012D1	F	#REF!	4	MTH-140 03 Integrated Precalc/Calc I
2012D1	B	#REF!	4	FYS-1103 EL Finding Your Life Purpose
2012D2	C+	#REF!	4	MTH-140 02 Integrated Precalc/Calc I
2012D2	C+	#REF!	4	HSC-101 13 Wellness Concepts	MB
2012D2	C+	#REF!	4	FYW-1192 01 The Lost Generation
2013D1	C-	#REF!	4	THA-101 03 Introduction to Theatre	VP
2013D1	C	#REF!	4	SOC-101 05 Introduction to Sociology	HB
2013D1	C+	#REF!	4	REL-125 02 Religions of the World	WC
2013D1	C-	#REF!	4	ECN-111 03 Introduction to Economics	HB
Sheet2 has table called ScaleTable as follows:

Grades	Points
A	4.00
A-	3.70
B+	3.30
B	3.00
B-	2.70
C+	2.30
C	2.00
C-	1.70
D+	1.30
D	1.00
D-	0.70
F	0.00

[/pre]
Here is my formula for cells in column C called Points:

=VLOOKUP([grade],ScaleTable[Grades],2,FALSE)


Could it be because each table has a column called Points ?

 

[jjamjatra]

Hi -

I discovered my problem. It was in the 2nd parameter to the function:


I changed this:


=VLOOKUP([grade],ScaleTable[Grades],2,FALSE)


to be:


=VLOOKUP([grade],ScaleTable[Grades]:ScaleTable[Points],2,FALSE)


and it works properly. I think I had misunderstood the requirements of the "where" parameter but now the 3rd parameter makes much more sense to me. Thanks for your time and interactions on this.