Multiple IF's to Select a Value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Using the following table, I want a formula that satifies multiple IF type
statements.

From To MTM Renew 10
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

For example, if one value is greater than 101 and less than 225, the value
is $31.04.

Any ideas?
 
One way:

=VLOOKUP(J1, A2:C6, 3, TRUE)

where J1 is your value, and the table you show is in A1:D6.
 
Thanks. I've used the SUMPRODUCT formula before so I want to stay with that.
I want to expand theformula to include the ranges from each row. For example,
if value is between 0 and 30, then C1, if between 31 and 100, then C2, and so
on. How would you suggest I write that formula?
 
=SUMPRODUCT(--(A2:A6<=F2),--(B2:B6>=F2),C2:C6)
Where your sample table is in A1:D6 and the value you are looking up is in
F2. Adjust as necessary.
If your table is on a different sheet from your value include sheet names:
=SUMPRODUCT(--(Sheet1!A2:A6<=C2),--(Sheet1!B2:B6>=C2),Sheet1!C2:C6)
Where your table is in A1:D6 on Sheet1 and the value is in C2 on a different
sheet.
 
Tue, 8 May 2007 08:34:02 -0700 from JerryS
Using the following table, I want a formula that satifies multiple IF type
statements.

From To MTM Renew 10
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

For example, if one value is greater than 101 and less than 225, the value
is $31.04.
Click to expand...

Use VLOOKUP with the fourth argument set to TRUE.

You don't need your first column at all, assuming it's impossible for
your input value to be less than 0.
 
Back
Top