Having trouble with VLookup function, pls help me.

  • Thread starter Thread starter Sandi
  • Start date Start date
S

Sandi

Hi,
I am new to Excel and I am having trouble using the Vlookup function. I
have two workbooks.

This is the data that I have in the first workbook:

Column E Column F

Overall Grade
94.00
92.67
52.33
90.33
65.67
66.67
52.00
73.67
67.00
81.00
66.00
91.33
90.33
88.67
92.67

2nd workbook

Column A Column B

Exam average Grade
0 to 49 = F
50 to 59 = D
60 to 74 = C
75 to 89 = B
90 to 100 =A

In the 2nd workbook this is what I need to do; in the range a2:b6 I have to
create a lookup table for the range of grades. Then I have to switch to the
first workbook and in column F I have to insert a lookup function to
calculate the final letter grade for each student based on the lookup table
that is to be created in the second workbook.

Please help me. I am having so much trouble with this. I just do not
understand it.

Thanks so much.
Sandi
 
Sandi

If you are committed to a VLOOKUP try re-arranging your grade data like so

ColB ColC
0 F
50 D
60 C
75 B
90 A

It looks a little strange to start with, but now if you put by your actual
scores the formulae

=VLOOKUP(E2,Sheet2!$B$1:$C$5,2,TRUE)

Vlookup takes the value 'up to' a match by using the last parameter set as
TRUE. Look up help for VLOKUP for a better explanation of this last
parameter
 
Option A

It's much simpler to collapse both books into one,
for example, to put the exam grade reference table in
the same book as the marks to be graded.

So, assuming this is the case (only 1 book),
this set-up should work for you:

In say, sheet 1, you have the exam grades ref table in A2:B6, viz:

0 F
50 D
60 C
75 B
90 A

Name this range A2:B6 as say: GradeTable

To name the range, select A2:B6
Click inside the namebox next to the formula bar
(the one with the drop arrow just to the left)
Type: GradeTable
Press Enter

Now, assuming the marks are in say,
a separate sheet 2, col E, E2 downwards
viz:

Overall
94
92.67
52.33
90.33
etc

In this sheet 3, put in F2: =VLOOKUP(E2,GradeTable,2,TRUE)
copy down col F as far as required

Col F will return the grades corresponding to the marks in col E

If desired, hide sheet 1 via Format > Sheet > Hide

Option B

As described in your post, you have 2 books:
Book1.xls for marks
Book2.xls for exam grade table

In Book2.xls, sheet 1

Create the named range: GradeTable
similarly as described in Option A above,
save the book to say: C:\WINDOWS\Desktop
and close the book

In Book1.xls,

Click Insert > Name > Define

Under "Names in workbook:"
put: GradeTable

In the "Refers to:" box,
put : ='C:\WINDOWS\Desktop\[Book2.xls]1'!$A$2:$B$6

click Add > OK

Now, in say sheet 1 of Book1,
you have the marks in col E, E2 downwards

put in F2: =VLOOKUP(E2,GradeTable,2,TRUE)
copy down col F as far as required

Col F will return the grades corresponding to the marks in col E
 
Back
Top