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!!!

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!!!

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!!!

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!!!