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 this:

=SUMPRODUCT((A2=B2:B3)*(A2<=C2:C3)*D2:D3)

--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"edm1007" wrote in message
...
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

LastRow = Range("B" & Rows.count).end(xlup).Row
Rating = 0
for RowCount = 2 to LastRow
if A2 = Range("B" & Rowcount) and _
A2 <= Range("C" & Rowcount) then

Rating = Range("D" & Rowcount)
exit for
end if
next RowCount
"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

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

Enter 5, 21, 56 ... (lower values of your intervals) in Col A, enter 7, 12,..
(the value you want to assign based on which interval it falls in in Col B,
Enter 12, 50,... (values to check in which interval it finds and assign the
value in Col B) in Col C.

Enter the following in D1 and copy down;
=VLOOKUP(C1,A:B,2,TRUE)

Let me know how it goes

"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

if you are looking for a formula solution use the following VLOOKUP.

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

You only need to look at column Bsince the values in column B are sorted and
are one larger than the value in Column C. Vllookup will find the largest
value in Column B that is less than or equal to the value you are looking
for, then return the value in column D whic is the 3rd column in the Table
range (B2:B100)

"Sheeloo" wrote:

Enter 5, 21, 56 ... (lower values of your intervals) in Col A, enter 7, 12,..
(the value you want to assign based on which interval it falls in in Col B,
Enter 12, 50,... (values to check in which interval it finds and assign the
value in Col B) in Col C.

Enter the following in D1 and copy down;
=VLOOKUP(C1,A:B,2,TRUE)

Let me know how it goes

"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

If you only have those 2 sets of values in B:D...

=IF(AND(B2<=A2,C2=A2),D2,IF(AND(B3<=A2,C3=A2),D3 ,""))

If you have "many" sets of values...

=IF(A2<B2,"",VLOOKUP(A2,B2:D5,3))

--
Biff
Microsoft Excel MVP


"edm1007" wrote in message
...
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

Thanks this produced the exact results I was looking for. Thanks a
million!!!!!!!!!!!!!

"Joel" wrote:

if you are looking for a formula solution use the following VLOOKUP.

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

You only need to look at column Bsince the values in column B are sorted and
are one larger than the value in Column C. Vllookup will find the largest
value in Column B that is less than or equal to the value you are looking
for, then return the value in column D whic is the 3rd column in the Table
range (B2:B100)

"Sheeloo" wrote:

Enter 5, 21, 56 ... (lower values of your intervals) in Col A, enter 7, 12,..
(the value you want to assign based on which interval it falls in in Col B,
Enter 12, 50,... (values to check in which interval it finds and assign the
value in Col B) in Col C.

Enter the following in D1 and copy down;
=VLOOKUP(C1,A:B,2,TRUE)

Let me know how it goes

"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

This is a very simple VLOOKUP problem that people are using exotic solutions
that aren't necessary. Column C is not need to solve this problem. column C
is usful for people who are reading the Table butr doesn't need to be used
when creating the formula. VLOOKUP has a nice feature that it will work on a
range of values, as well as, exact values!

"T. Valko" wrote:

If you only have those 2 sets of values in B:D...

=IF(AND(B2<=A2,C2=A2),D2,IF(AND(B3<=A2,C3=A2),D3 ,""))

If you have "many" sets of values...

=IF(A2<B2,"",VLOOKUP(A2,B2:D5,3))

--
Biff
Microsoft Excel MVP


"edm1007" wrote in message
...
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