Selecting specific value within a range

Hi there,

I need assistance with the following problem:

I have the following matrix-

Indicator Score Range %
A 114 + 10%
B 110.50 to 113.99 9%
B 106.50 to 110.49 8.5%
B 105.00 to 106.49 7%
C 100.50 to 104.99 6%
C 91.50 to 100.49 5.5%
C 90.00 to 91.49 5%
D 80.50 to 89.99 4%
D 71.50 to 80.49 3.5%
D 70.00 to 71.49 3%
E 51.00 to 69.99 2%
F 40.00 to 50.99 0%
O 6%

The percentages may change going forward.
I have a spreadsheet with Employee details and each employee has a Indicator and a Score.
I'm battling to get Excel to do a lookup on the Indicator, find the applicable Score Range, see if the individual's Score falls within the range and then inserts the relevant % into a cell in a specific row of each employee.

If we did not have more than 2000 doing it manualy might have been an option. Also, the Percentages and Score Ranges in the matrix might change a couple of times.

Anyone able to asssist?

Thanking you in advance!

Rennier (South Africa)

Indicator             Score Range           %
A                     114 +                 10%
B                     110.50 to 113.99      9%
B                     106.50 to 110.49      8.5%
B                     105.00 to 106.49      7%
C                     100.50 to 104.99      6%
C                     91.50 to   100.49     5.5%
C                     90.00 to   91.49      5%
D                     80.50 to   89.99      4%
D                     71.50 to   80.49      3.5%
D                     70.00 to   71.49      3%
E                     51.00 to   69.99      2%
F                     40.00 to   50.99      0%
O                                           6%

The attached sheet shows a simple VLOOKUP doing what you want. You don't need to describe the range, just the break points, and the data needed to be in descending format. Pretty straightforward.

Wasn't sure how you wanted to utilize the letter grade indicators since scores are numeric and more accurate that way.

If you arrange your data as attached... so that the scores are separated from upper scale and lower scale and also listed in ascending order then you can do a simple LOOKUP()

=LOOKUP(B20,B2:D14)

Okay, I probably made it more complicated than it needed to be (see attached) but I have the user entering the Indicator and the Score and the spreadsheet telling them if the score matches the indicator. I also used MATCH and INDEX to keep the top to bottom scale.
With the Table in A16:C28, and indicator entered into E17, Score into F17; the formula to check to see if they are compatable is

=IF(INDEX($A$16:$A$28,MATCH(F17,$B$16:$B$28,-1),1)=E17,"Yes","No")

and the formula to get the percent is

=INDEX($C$16:$C$28,MATCH(F17,$B$16:$B$28,-1),1)