[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
• Status: Solved
• Priority: Medium
• Security: Public
• Views: 310

# VLOOKUP Excel 2010

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
perla1962
• 3
• 3
• 3
• +2
2 Solutions

Commented:
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

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

Sincerely,
Ed
0

Author Commented:
I'm trying to used the VLOOKUP FUNCTION.
0

Commented:
0

Commented:
0

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

No possible solutions.
0

Commented:
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

Commented:
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

Microsoft MVP ExcelCommented:
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

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

Commented:
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

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

Commented:
heheheh - got that right.
0

Author Commented:
Thank you very much, I got it.
0

## Featured Post

• 3
• 3
• 3
• +2
Tackle projects and never again get stuck behind a technical roadblock.