Difficult Lookup Problem

[Berrett]

I have what I think is a difficult problem with a Lookup formula. My situation is that I have students in five different grades (K-4) each with a score on a certain assessment instrument.

I need to assign each student to a proficiency level (LOW, MIDDLE, or HIGH) based on their score. The problem is that the cut-points for proficiency are different for each grade. For example:

In grade K students need to be assigned to one of the following groups:

Score = 0-3 = LOW
Score = 4-7 = MIDDLE
Score = 8-20 = HIGH

In Grade 1 the groups are:

Score = 0-6 = LOW
Score = 7-10 = MIDDLE
Score = 11-20 = HIGH

And so on... Each grade is different. All of the kids are in one big list. There is a column for GRADE and a column for SCORE. There are other columns too that are relevant for this problem.

Can anyone help?

 

[colbymack]

You can create a table that has the value of grade and score combined into one cell - i.e. K - 1, K-2, and so on. The next column would have the ranking of Low, Middle, High. You can then do a vlookup on that table from your scores to this table to find the ranking.

Hope this helps.

 

[Berrett]

Hmmm... I'll mess around with that and see if I can get it to work. Thanks for the reply.

 

[barry houdini]

You could probably work with a smaller table - but it might mean a bigger formula

If you have this table in F1:K4 (note: F1 is blank)

k 1 2 3 4
low 0 0 0 0 0
medium 4 7 10 11 13
high 8 11 14 15 17

which shows the grades at the top and for each grade the lower bound of each category, e.g. in this example grade 4 is low 0-12, medium, 13-16 and high 17-20

then, assuming student names in A2 down, grade B2 down, score C2 down then in D2 copied down

=CHOOSE(MATCH(C2,INDEX($G$2:$K$4,0,MATCH(B2,$G$1:$K$1,0))),$F$2,$F$3,$F$4)

 

[adam_neb]

If you have a worksheet with cells that contain the student's grade and score, you can do this with a nested v-lookup:

On a separate tab first create a matrix (when I tested this I called the range "PROFICIENCY"). The matrix I created was in A1:F22. Column Headings were Score/K/1/3/3/4. A2:A22 contained scores of 0 through 20. Enter the Proficiency vaules for each score under each grade (Low, Medium, High).

Next create a matrix that indicates which grade in which column (when I tested this i called the range "columns").
K 2
1 3
2 4
3 5
4 6

On the worksheet that contains student data (grade and score) you can enter the following formula:

=VLOOKUP(C2,PROFICIENCY,VLOOKUP(B2,COLUMN,2,FALSE),FALSE)

Where C2 contains the score and B2 contains the grade.

For each grade this forula will bring over the correct Proficiency level for the students score.

 

[Berrett]

Adam_Neb - this worked for me. Thanks a lot. Both approaches seem to work (yours and houdini's) - so now I have what I need. Thanks a ton for the help. This will really save me a lot of time.

Thanks

 

[colbymack]

I like adam_neb's solution as well. I will use that in the future - that was one I did not know - nesting the vlookups.

 

[barry houdini]

I took another look at this and simplified the formula as shown

Book1
	A	B	C	D	E	F	G	H	I	J	K	L
1		grade	score	level			k	1	2	3	4
2	student a	3	3	low		low	0	0	0	0	0
3	student b	1	13	high		medium	4	7	10	11	13
4	student c	2	20	high		high	8	11	14	15	17
5	student d	k	4	medium
6	student e	4	18	high
7	student f	1	17	high
8	student g	3	13	medium
9	student h	k	6	medium
10
Sheet1

Formula in D2 copied down column

=LOOKUP(C2,INDEX(G$2:K$4,0,MATCH(B2,G$1:K$1,0)),F$2:F$4)