SUMPRODUCT or VLOOKUP
 

I've asked this question before today but I'm still not getting what I need
so I'm resubmitting with better clarity.

I have a sheet that has numerous rows and columns. I want to add a column
that contains a value if multiple cells meet certain requirements. Here is
the table I refer to: A1:D6

From To MTM Renew
0 30 $32.39 $17.60
31 100 $31.94 $15.35
101 225 $31.04 $14.00
226 450 $30.59 $12.20
451 1000 $29.24 $10.40

The data sheet looks like this: A1:D7

PROPERTY RATE MTM Renew STD
A G EDWARDS $15.00 20
LAKE ROBBINS $5.45 55
AFTONSHIRE PLACE $2.27 87
ALVIN & ASSOC $0.25 236
ASHLAND COURT $1.22 101
ASTRO CITY $12.97 17

What I want it to do is to follow this logic:
If D2 is 0 and < 30, then the Renew value is 17.60. If D2 is 31 and < 100,
the the Renew value is $15.35 and so on following the ranges of the first
table. I've had suggestions to do a VLOOKUP and a SUMPRODUCT formula but I
can't get my head around which is better and how to do it. Any suggestions?
Thanks
--
JerryS

SUMPRODUCT or VLOOKUP
 

JerryS

Why not just:

=IF(D2<=30,17.60,IF(D2<=100,15.35,IF(D2<=225,14,IF (D2<=450,12.2,IF(D2<=1000,10.4,
"out of range")))))

Regards

Trevor


"JerryS" wrote in message
...
I've asked this question before today but I'm still not getting what I
need
so I'm resubmitting with better clarity.

I have a sheet that has numerous rows and columns. I want to add a column
that contains a value if multiple cells meet certain requirements. Here is
the table I refer to: A1:D6

From To MTM Renew
0 30 $32.39 $17.60
31 100 $31.94 $15.35
101 225 $31.04 $14.00
226 450 $30.59 $12.20
451 1000 $29.24 $10.40

The data sheet looks like this: A1:D7

PROPERTY RATE MTM Renew STD
A G EDWARDS $15.00 20
LAKE ROBBINS $5.45 55
AFTONSHIRE PLACE $2.27 87
ALVIN & ASSOC $0.25 236
ASHLAND COURT $1.22 101
ASTRO CITY $12.97 17

What I want it to do is to follow this logic:
If D2 is 0 and < 30, then the Renew value is 17.60. If D2 is 31 and <
100,
the the Renew value is $15.35 and so on following the ranges of the first
table. I've had suggestions to do a VLOOKUP and a SUMPRODUCT formula but I
can't get my head around which is better and how to do it. Any
suggestions?
Thanks
--
JerryS