Excel Lookup Help!

[Guest]

I need a foumla to do the following.

Look at column C and determine if it is between the ranges in column M and
column N, and then i need it to look at column AB and determine if the info
in column AB is between the ranges in column O and P. Once these two are
determined i need it to return the corresponing value in column Q.

ANY IDEAS?
HERE IS WHAT MY TABLE LOOKS LIKE

M N O P Q
$0.00 $1,499,999.99 0 1.99
0.10%
$0.00 $1,499,999.99 2 2.99
0.20%
$0.00 $1,499,999.99 3 3.99
0.40%
$0.00 $1,499,999.99 4 4.99
0.65%
$0.00 $1,499,999.99 5 100 0.80%
$1,500,000.00 $1,999,999.99 0 1.99 0.10%
$1,500,000.00 $1,999,999.99 2 2.99 0.25%
$1,500,000.00 $1,999,999.99 3 3.99 0.55%
$1,500,000.00 $1,999,999.99 4 4.99 0.85%
$1,500,000.00 $1,999,999.99 5 100 1.00%
$2,000,000.00 $2,999,999.99 0 1.99 0.15%
$2,000,000.00 $2,999,999.99 2 2.99 0.30%
$2,000,000.00 $2,999,999.99 3 3.99 0.70%
$2,000,000.00 $2,999,999.99 4 4.99 1.00%
$2,000,000.00 $2,999,999.99 5 100 1.20%
$3,000,000.00 $3,999,999.99 0 1.99 0.15%
$3,000,000.00 $3,999,999.99 2 2.99 0.35%
$3,000,000.00 $3,999,999.99 3 3.99 0.80%
$3,000,000.00 $3,999,999.99 4 4.99 1.20%
$3,000,000.00 $3,999,999.99 5 100 1.30%
$4,000,000.00 $5,999,999.99 0 1.99 0.20%
$4,000,000.00 $5,999,999.99 2 2.99 0.40%
$4,000,000.00 $5,999,999.99 3 3.99 0.90%
$4,000,000.00 $5,999,999.99 4 4.99 1.35%
$4,000,000.00 $5,999,999.99 5 100 1.55%
$6,000,000.00 $1,000,000,000.00 0 1.99 0.20%
$6,000,000.00 $1,000,000,000.00 2 2.99 0.40%
$6,000,000.00 $1,000,000,000.00 3 3.99 1.00%
$6,000,000.00 $1,000,000,000.00 4 4.99 1.50%
$6,000,000.00 $1,000,000,000.00 5 100 1.70%

 

[RagDyer]

One way,
Try this:

=IF(AND(C1>=M1,C1<=N1,AB1>=O1,AB1<=P1),Q1,"Out Of Range")

 

[JW]

I need a foumla to do the following.

Look at column C and determine if it is between the ranges in column M and
column N, and then i need it to look at column AB and determine if the info
in column AB is between the ranges in column O and P. Once these two are
determined i need it to return the corresponing value in column Q.

ANY IDEAS?
HERE IS WHAT MY TABLE LOOKS LIKE

M N O P Q
$0.00 $1,499,999.99 0 1.99
0.10%
$0.00 $1,499,999.99 2 2.99
0.20%
$0.00 $1,499,999.99 3 3.99
0.40%
$0.00 $1,499,999.99 4 4.99
0.65%
$0.00 $1,499,999.99 5 100 0.80%
$1,500,000.00 $1,999,999.99 0 1.99 0.10%
$1,500,000.00 $1,999,999.99 2 2.99 0.25%
$1,500,000.00 $1,999,999.99 3 3.99 0.55%
$1,500,000.00 $1,999,999.99 4 4.99 0.85%
$1,500,000.00 $1,999,999.99 5 100 1.00%
$2,000,000.00 $2,999,999.99 0 1.99 0.15%
$2,000,000.00 $2,999,999.99 2 2.99 0.30%
$2,000,000.00 $2,999,999.99 3 3.99 0.70%
$2,000,000.00 $2,999,999.99 4 4.99 1.00%
$2,000,000.00 $2,999,999.99 5 100 1.20%
$3,000,000.00 $3,999,999.99 0 1.99 0.15%
$3,000,000.00 $3,999,999.99 2 2.99 0.35%
$3,000,000.00 $3,999,999.99 3 3.99 0.80%
$3,000,000.00 $3,999,999.99 4 4.99 1.20%
$3,000,000.00 $3,999,999.99 5 100 1.30%
$4,000,000.00 $5,999,999.99 0 1.99 0.20%
$4,000,000.00 $5,999,999.99 2 2.99 0.40%
$4,000,000.00 $5,999,999.99 3 3.99 0.90%
$4,000,000.00 $5,999,999.99 4 4.99 1.35%
$4,000,000.00 $5,999,999.99 5 100 1.55%
$6,000,000.00 $1,000,000,000.00 0 1.99 0.20%
$6,000,000.00 $1,000,000,000.00 2 2.99 0.40%
$6,000,000.00 $1,000,000,000.00 3 3.99 1.00%
$6,000,000.00 $1,000,000,000.00 4 4.99 1.50%
$6,000,000.00 $1,000,000,000.00 5 100 1.70%

 

[Guest]

Thansk for the reply.
I dont think this is giving me what i need.

I need it to look at column C and determine if it is between the ranges in
column M and column N, and then also look at column AB and determine if it is
between the ranges in column O and P. Once these two are determined i need
it to use that information and find the corresponing value in column Q.

Example: if the amount is 2.8 million, and the score is 2.75, then the
payout percent is .30%

I am probably not explaining this very well so if you need more info please
let me know.

 

[Guest]

i need it to look at the whole coulmn for M, N, O, and P and return the
coressponding value in column Q. i think i need to combine two "lookup"
formulas?

 

[RagDyer]

When you used column *names* in describing your scenario, you made it sound
that *every* row in Column C and Column AB would have a value that you
wanted compared to it's relative row in the other columns.

The formula that JW and I suggested was made to be entered in an adjoining
column, and *copied down* that column, to compare the values in Columns C
and AB, row by row.

I now assume you want to compare a *single* cell in Column C and AB, say C1
and AB1, and have those 2 values poll the entire datalist.

So, with your datalist in M1 to Q30, and values entered in C1 and AB1, try
this formula:

=SUMPRODUCT((M1:M30<=C1)*(N1:N30>=C1)*(O1:O30<=AB1)*(P130>=AB1)*Q1:Q30)