Solved

# VLOOKUP Excel 2010

Posted on 2011-10-29
247 Views
Hi,
I need your help to complete the VLOOKUP formula for my sister who grading her students.  I need to assign letter grades base upon points.

The points for the course is 600

To earn an "A", the student must have 540 or more points.
To earn a "B", the student must have 480 - 539 points.
To earn a "C", the student must have 420 - 479 points.
To earn a "D", the student must have 360 - 419 points.
Less than 360 points, earns an "F".

Mary              550
Sue              479
Paulo              200
Rena              420
Stanford              366

0
Question by:perla1962

LVL 19

Expert Comment

Try this:

=IF(E2<360,"F",IF(AND(E2>=360,E2<=419),"D",IF(AND(E2>=420,E2<=479),"C",IF(AND(E2>=480,E2<=539),"B","A"))))

Ed
0

LVL 19

Expert Comment

The formula above assumes that the Total Points in column E.

Sincerely,
Ed
0

Author Comment

I'm trying to used the VLOOKUP FUNCTION.
0

LVL 5

Expert Comment

0

LVL 5

Expert Comment

0

Author Comment

I've requested that this question be deleted for the following reason:

No possible solutions.
0

LVL 19

Expert Comment

With the data you provided, VLOOKUP function is not possible. That is the reason behind I suggested the IF function for it is the appropriate function to be used in your scenario.

Sincerely,
Ed
0

LVL 41

Accepted Solution

Why would you delete this question, so easily?  When you post a question in E-E, its a good idea to let the question sit for a while while E-E experts provide possible solutions.

There most certainly is a possible solution using VLOOKUP.  The form of VLOOKUP to use is:

=Vlookup(score, table, TRUE) <- score being a score from 0-600, table having an appropriate vlookup table that captures the minimum, each inflection, and the maximum for each possible result, F, D, C, B, and A.

The table looks like this:

0      F
359      F
360      D
419      D
420      C
479      C
480      B
539      B
540      A

See attached worksheet, with this vlookup command on the sample data, and all the results are correct.  In addition, to prove that this solution is correct, I created 601 scores - from 0 to 600, with the vlookup result.  As you can see, the results are 100% correct.

Please accept this solution as the correct one, rather than deleting the question.

See attached.

Cheers!

Dave
0

LVL 50

Assisted Solution

Hello,

first of all, "this cannot be done" may be a valid answer and is no reason to delete the question.

Second, it CAN be done with a vlookup formula. Either create a lookup table, for example starting in G1

0      E
360      D
420      C
480      B
540      A

Then the vlookup in cell C2 would be

=VLOOKUP(B2,\$G\$1:\$H\$5,2,1)

If you don't want to create a lookup table, you can embed it in the formula, see column D in the attached spreadsheet

=VLOOKUP(B2,{0,"E";360,"D";420,"C";480,"B";540,"A"},2,1)

cheers, teylyn
Book1.xlsx
0

LVL 50

Expert Comment

Dave, why the duplication in the lookup table? If the last parameter is TRUE, then the next smallest matching result will be returned.
0

LVL 41

Expert Comment

You of course are correct.  Not sure what I was thinking.  I thought I built the table as you had but probably one less on each inflection - re: 359, 419, 479, 539 and found I needed the next point up, not giving it another thought afterwards, versus pulling my initial values out.  Must have been I was seeing a bit red on this one given the nature of the "quick closure" lol.

While mine's "technically" correct, yours is more efficient and elegant.  Its been a while since I used a vlookup to grade something - used to do it all the time, re: finding quartile,deciles,etc... lol

Cooler heads prevailed, and yours is much cooler :)

Dave
0

LVL 50

Expert Comment

Yeah, well, can't get much more chilled than at the end of a nice, sunny Sunday with the kids tucked in bed ... :-)
0

LVL 41

Expert Comment

heheheh - got that right.
0

Author Comment

Thank you very much, I got it.
0

## Featured Post

### Suggested Solutions

I've recently been in need of an Excel macro that could add a letter before the text on multiple cells in an Excel document. My English is as it is, so I will try explain what it does diffrently. If you have an excel document with 2000 rows an…
Dealing with unintended Excel Active-X resizing quirks (VBA code simulates "self correction") David Miller (dlmille) Intro Not everyone is a fan of Active-X controls in spreadsheets (as opposed to the UserForm approach, the older Form controls …
This Micro Tutorial will demonstrate on a Mac how to change the sort order for chart legend values and decrpyt the intimidating chart menu.
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.