Excel Master needed - Too tough to tame

[RiverCity]

Is the below do-able in a formula in Excel 2007? Am open to suggestion/help.

I have an excel 2007 workbook with multiple tabs. On the "data" worksheet I
have 50+ unique trait scores per individual. I want to take one of the
scores, match to a set of ranges (on the "range" worksheet) to see where it
fits. (the range magnitude may vary depending on the trait being scored - so
there may be 50+ different range series - one range series for each trait)
Once the trait score is matched to a range, I want to assign the trait score
a specific number of points based upon the priority level that the trait has
in the overall scoring of that individual.

"Data tab" info would look something like this:
A B C D E
1 Trait 1 Trait 2 Trait 3 Trait 4 Trait 5
2 36 9 10 17 15

"Range tab" would have 2 sets of range tables. One to determine the range a
trait score fits to (sort of like in school a score of 81 fits to the range
of 78-84 which is a C), the other range table is where the points assigned to
the trait score are determined, based upon the "level of importance" that the
trait itself carried. The ranges a score would fit into would look
something like this:

Importance Range 1 Range 2 Range 3 Range 4
Level From To From To From To
From To
Trait 1 1 0 14 15 25 26 33
34 50
Trait 2 2 0 10 11 20 21 40
41 55
Trait 3 3 5 15 16 22 23 43
44 70

The points chart would look like this:

Importance Range 1 Range 2 Range 3 Range 4
Level Points Points Points Points
3 10 5 3 1
2 5 3 2 1
1 3 2 1 0

So then, if I wanted to get a "points score" for Trait 1 with a Trait Score
of 36 on the "data" tab, I would see that on the "range tab" it fit in Range
4 (34-50) and since the Importance Level was a 1, it would return a value of
1 (point). Trait 2 with a Trait Score of 9 on the "data" tab would fit in
Range 1 (0-10) and with an Importance Level of 2 it would return a value of 5
(points).

 

[Sheeloo]

I tried the setup below;

In Data tab entered
B1=Trait1, B2=Trait2 and so on
in row 2 starting at B2 entered trait scores for the individual in A1
in B3 entered
=MATCH(B1,Range!$A:$A,0) to get the row no for trait in B1 on Range tab
in B4 entered
=INT((MATCH(B2,INDIRECT("Range!"&B3&":"&B3),1)-1)/2) to get the range the
score falls in
in B5 entered
=INDEX(Range!$A$1:$J$100,B3,2) to get the importance for the trait
in B6 entered
=VLOOKUP(B5,Value!$A:$E,Data!B4+1,FALSE) to get the final result
I named the tab, which contains the final value for the given importance and
range, as Value

You can merge the formulas once you test them out and understand the logic...

If you copy it to the right, it will work for different traits and scores...
You need to take care of refrences if you copy it down...

btw answer should be 0 for trait 1, range 4, importance 1...

 

[RiverCity]

Sheeloo, this is your world, I'm just glad you let me live in it. Spot-on
was your work. I struggled with this Friday thru Monday morning, coming up
with a much more complex and balky solution. In a matter of minutes you
constructed the answer - perfectly. There was nothing on the web that
remotely resembled this issue in this format. Much, much thanks and
appreciation. I owe you one.