nested lookups or ???

Trying to make sales scoring sheet that automatically assigns a score from a table that has different scoring scales and assigned points based on scale.

Something like this...

scale sales% points
5 50 0
5 75 3
5 100 5
10 50 0
10 75 7
10 100 10
15 50 5
15 75 10
15 100 15

The form would have a row where a manager could enter lets say...

d1=scale 5, 10, 0r 15
d2=% of sales made
d3=point score returned from column c

I've tried using varying degrees of nested "IF" and "Lookups", but I either exceed the magic 7 number error or I enter too many arguments.

Help?

Originally Posted by protonLeah

Have a look:
Attachment 33755

Thanks!

I'll play with that for a while and see if I can get it to work.

One thing though,

When the % sales is entered, it may fall between the ranges.

For instance, 77% would be in the range for 75% to 100%, and scored thusly.

When I entered a % like that, it returned #n/a.

Originally Posted by JBeaucaire

If your chart is complete and it's not going to expand, a simple tiered lookup suffices...less elegant, but workable.

Thanks.

That's where I went before. But in the actual table, there's 10 different score bands, so I exceed the 7 rule.

Originally Posted by JBeaucaire

Well, you have to think ahead. That sounds more like a simple Table format. Once your data is in a table, an INDEX/MATCH process will do the trick.

Attached.

Thanx!

I'll give that a whirl!