Complex Index Match Help (or at least complex to me)

[Jennifer Reitman]

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

[Bob Phillips]

=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" <[email protected]> wrote in message
news:[email protected]...
> 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!!!
>

[Jennifer Reitman]

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" <[email protected]> wrote in message
> news:[email protected]...
> > 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!!!
> >

[Bob Phillips]

layout some data for me.

--
HTH

Bob Phillips

(replace somewhere in email address with gmail if mailing direct)

"Jennifer Reitman" <[email protected]> wrote in message
news:[email protected]...
> 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" <[email protected]> wrote in message
> > news:[email protected]...
> > > 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!!!
> > >
>