On Sat, 6 Sep 2008 09:11:02 -0700, edm1007
wrote:

I hope this is not confusing but what is displayed as the final rating for A2
depends on if it falls between the values in B2 and C2 or B3 and C3
respectively

for instance

A2 = 10

B2 = 5
C2 = 20
D2 = 7

So since A2 falls between B2 and C2 the rating for A2 would equal 7 because
that is the respective rating.

but if A2 = 50

B3 = 21
C3 = 55
D3 = 12

then the rating for A2 would be 12 since it falls between B3 and C3.

Thanks

Try the following formula:

=SUMPRODUCT(--(A2=B$2:B$3),--(A2<=C$2:C$3),D$2:D$3)

Change the 3's if you have more rows with possible intervals and
ratings

If there are no gaps in the intervals the following formula can be
used:

=VLOOKUP(A2,B$2:D$3,3)

Change D3, but not the 3, to reflect the last row of
intervals/ratings.

Hope this helps. / Lars-Åke