Difficult Lookup Problem


Board Regular
Aug 6, 2004
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?

Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.
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.
Upvote 0
Hmmm... I'll mess around with that and see if I can get it to work. Thanks for the reply.
Upvote 0
You could probably work with a smaller table - but it might mean a bigger formula :cry:

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

Upvote 0
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:


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.
Upvote 0
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.

Upvote 0
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.
Upvote 0
I took another look at this and simplified the formula as shown
2student a33lowlow00000
3student b113highmedium47101113
4student c220highhigh811141517
5student dk4medium
6student e418high
7student f117high
8student g313medium
9student hk6medium

Formula in D2 copied down column

Upvote 0

Forum statistics

Latest member

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back