Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Index Match Help (or at least complex to me)
I have a index match that needs additional criteria. The formula
references a separate sheet called "INPUTS" and is referencing a number associated with a price that looks like this: cost size ..80 124 ..90 148 1.00 156 (and so on) here is the formula INDEX(Inputs!$A$456:$A$461,MATCH(B6,Inputs!$B$456: $B$461,0))*B36) - the multiple at the end is a total quantity. the b6 is the size Here is what I am stuck with. I want to add another criteria for the quantity. So something that would add in match (quantity less than 30000,quantity greater than 30,000 but less than 40,000, greater than 40,000 but less than 50,000) at the end of the day I am looking for something to do this: If the size is 124 AND the quantity is 30,000, then multiply the quantity by ..80) maybe it should be VBA. thanks for any help!!! |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Index Match Help (or at least complex to me)
=INDEX(Inputs!$A$4:$A$46,MATCH(1,(B6=Inputs!$B$4:$ B$46)*(Inputs!$C$4:$C$46=
30000)*(Inputs!$C$4:$C$46<40000),0))*B36 which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jennifer Reitman" wrote in message ups.com... I have a index match that needs additional criteria. The formula references a separate sheet called "INPUTS" and is referencing a number associated with a price that looks like this: cost size .80 124 .90 148 1.00 156 (and so on) here is the formula INDEX(Inputs!$A$456:$A$461,MATCH(B6,Inputs!$B$456: $B$461,0))*B36) - the multiple at the end is a total quantity. the b6 is the size Here is what I am stuck with. I want to add another criteria for the quantity. So something that would add in match (quantity less than 30000,quantity greater than 30,000 but less than 40,000, greater than 40,000 but less than 50,000) at the end of the day I am looking for something to do this: If the size is 124 AND the quantity is 30,000, then multiply the quantity by .80) maybe it should be VBA. thanks for any help!!! |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Index Match Help (or at least complex to me)
Hmm. but the <=30,000 (and series of numbers) was also going to be in
the table as a 3rd column, matching another cell (like the match for b6) so my question was how to add another match and index into the formula so there would be two matching criteria further complicated by the less than, greater than aspect. Bob Phillips wrote: =INDEX(Inputs!$A$4:$A$46,MATCH(1,(B6=Inputs!$B$4:$ B$46)*(Inputs!$C$4:$C$46= 30000)*(Inputs!$C$4:$C$46<40000),0))*B36 which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jennifer Reitman" wrote in message ups.com... I have a index match that needs additional criteria. The formula references a separate sheet called "INPUTS" and is referencing a number associated with a price that looks like this: cost size .80 124 .90 148 1.00 156 (and so on) here is the formula INDEX(Inputs!$A$456:$A$461,MATCH(B6,Inputs!$B$456: $B$461,0))*B36) - the multiple at the end is a total quantity. the b6 is the size Here is what I am stuck with. I want to add another criteria for the quantity. So something that would add in match (quantity less than 30000,quantity greater than 30,000 but less than 40,000, greater than 40,000 but less than 50,000) at the end of the day I am looking for something to do this: If the size is 124 AND the quantity is 30,000, then multiply the quantity by .80) maybe it should be VBA. thanks for any help!!! |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
Complex Index Match Help (or at least complex to me)
layout some data for me.
-- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jennifer Reitman" wrote in message oups.com... Hmm. but the <=30,000 (and series of numbers) was also going to be in the table as a 3rd column, matching another cell (like the match for b6) so my question was how to add another match and index into the formula so there would be two matching criteria further complicated by the less than, greater than aspect. Bob Phillips wrote: =INDEX(Inputs!$A$4:$A$46,MATCH(1,(B6=Inputs!$B$4:$ B$46)*(Inputs!$C$4:$C$46= 30000)*(Inputs!$C$4:$C$46<40000),0))*B36 which is an array formula, it should be committed with Ctrl-Shift-Enter, not just Enter. -- HTH Bob Phillips (replace somewhere in email address with gmail if mailing direct) "Jennifer Reitman" wrote in message ups.com... I have a index match that needs additional criteria. The formula references a separate sheet called "INPUTS" and is referencing a number associated with a price that looks like this: cost size .80 124 .90 148 1.00 156 (and so on) here is the formula INDEX(Inputs!$A$456:$A$461,MATCH(B6,Inputs!$B$456: $B$461,0))*B36) - the multiple at the end is a total quantity. the b6 is the size Here is what I am stuck with. I want to add another criteria for the quantity. So something that would add in match (quantity less than 30000,quantity greater than 30,000 but less than 40,000, greater than 40,000 but less than 50,000) at the end of the day I am looking for something to do this: If the size is 124 AND the quantity is 30,000, then multiply the quantity by .80) maybe it should be VBA. thanks for any help!!! |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Match Index | Excel Worksheet Functions | |||
Returning row # using match or index of repeated text in a complex table | Excel Worksheet Functions | |||
Match or Index Question | Excel Worksheet Functions | |||
Match & Index | Excel Worksheet Functions | |||
Vlookup, Index & Match | Excel Worksheet Functions |