| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Excel Lookup Help!
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% |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Lookup Help!
One way,
Try this: =IF(AND(C1=M1,C1<=N1,AB1=O1,AB1<=P1),Q1,"Out Of Range") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jeison" wrote in message ... 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% |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Lookup Help!
=IF(AND(C2=M2,C2<=N2,AB2=O2,AB2<=P2),Q2,"Whateve r")
jeison wrote: 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% |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Lookup Help!
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. "RagDyer" wrote: One way, Try this: =IF(AND(C1=M1,C1<=N1,AB1=O1,AB1<=P1),Q1,"Out Of Range") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jeison" wrote in message ... 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% |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Lookup Help!
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? "jeison" wrote: 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% |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Excel Lookup Help!
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 )*(P1:P30=AB1)*Q1:Q30) -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jeison" wrote in message ... 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. "RagDyer" wrote: One way, Try this: =IF(AND(C1=M1,C1<=N1,AB1=O1,AB1<=P1),Q1,"Out Of Range") -- HTH, RD --------------------------------------------------------------------------- Please keep all correspondence within the NewsGroup, so all may benefit ! --------------------------------------------------------------------------- "jeison" wrote in message ... 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% |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| Get Cell Address From Lookup (Alternative to Lookup) | Excel Worksheet Functions | |||
| Join 2 Lists - Lookup value in 1 list & use result in 2nd lookup | Excel Worksheet Functions | |||
| Sumproduct - Condition based on lookup of a Lookup | Excel Discussion (Misc queries) | |||
| SOS!!! lookup Excel vs Outlook? Record messages to Excel | Excel Discussion (Misc queries) | |||
| Pivot table doing a lookup without using the lookup function? | Excel Discussion (Misc queries) | |||
Linear Mode
