# Excel: Return value based on another value

September 27, 2009 at 17:22:11
Specs: Windows XP

 I am trying to rate employees.In col C I enter their points from a test (from 0 to 10)In col B I would like to return a "grade" from 1 to 4 based on how many points they get in col C based on the following condition: 0-2 points gives a grade 1, 3-7 point gives a grade 2, 8-9 points gives a grade 3, 10 points gives a grade 4.I can't figure out how to do that - can anyone help me?

See More: Excel: Return value based on another value

#1
September 27, 2009 at 18:03:53

 Hi,The VLOOKUP function will do this for you.First create a table with scores and gradesI used cells F2 to G12Possible scores are in column F and the grade for the score is in the adjacent cell in column G``` F G 2 0 1 3 1 1 4 2 1 5 3 2 6 4 2 7 5 2 8 6 2 9 7 2 10 8 3 11 9 3 12 10 4 ```Enter scores in column CEnter the following formula in column BIf first score is in C2then in B2 enter=VLOOKUP(C2,\$F\$2:\$G\$12,2,FALSE)Note the \$ signs.The formula looks up the value in C2 in cells F2 to F12Then, on finding a match it returns the grade from the second column of your table - hence the 2 in the formulaFalse ensures that the formula finds an exact matchDrag the formula in B2 down as many rows as you have scores.Regards

Report •

#2
September 27, 2009 at 19:26:32

 Here's a classic (and rather longish) =IF() solution:=IF(C1=10,4,IF(AND(C1<=9,C1>=8),3,IF(AND(C1<=7,C1>=3),2,IF(C1<=2,1,""))))MIKEhttp://www.skeptic.com/

Report •

#3
September 28, 2009 at 07:38:28

 Hi both Humar and Mike.Thanks for both of your repliles - they both did the trick in deed. I do appreciate your effort - you practically saved my day :-)SÃ¸ren, Denmark

Report •

Related Solutions

#4
September 28, 2009 at 08:12:47

 You're welcome

Report •