Index/Match Multiple Criteria

[EstherJ]

I have the following Index/Match formula which will return the value of 9 if
X17=CCC
X18=ORDERQTY

=INDEX($C$3:$F$5,MATCH(X17,$A$3:$A$5,0),MATCH(X18,$C$2:$F$2,0))

Apr-06 Apr-06 Apr-06 Apr-06
CUST PROD QTY VALUE ORDERQTY ORDERVALUE
AAA 999 1 4 7 10
BBB 888 2 5 8 11
CCC 777 3 6 9 12

How do I make it look at a thrid criteria of the date in row 1. Or perhaps
even a fourth criteria of PROD in column B?

Thanks,

Esther

[Domenic]

Try the following formulas, which need to be confirmed with
CONTROL+SHIFT+ENTER, not just ENTER...

> How do I make it look at a thrid criteria of the date in row 1.

=INDEX($C$3:$F$5,MATCH(H2,$A$3:$A$5,0),MATCH(1,($C$1:$F$1=J2)*($C$2:$F$2=
K2),0))

....where H2 contains the 'Cust', J2 contains the 'Date', and K2 contains
'OrderQty'.

> Or perhaps even a fourth criteria of PROD in column B?

=INDEX($C$3:$F$5,MATCH(1,($A$3:$A$5=H2)*($B$3:$B$5=I2),0),MATCH(1,($C$1:$
F$1=J2)*($C$2:$F$2=K2),0))

....where H2 contains the 'Cust', I2 contains the 'Prod', J2 contains the
'Date', and K2 contains 'OrderQty'.

Hope this helps!

In article <[email protected]>,
EstherJ <[email protected]> wrote:

> I have the following Index/Match formula which will return the value of 9 if
> X17=CCC
> X18=ORDERQTY
>
> =INDEX($C$3:$F$5,MATCH(X17,$A$3:$A$5,0),MATCH(X18,$C$2:$F$2,0))
>
> Apr-06 Apr-06 Apr-06 Apr-06
> CUST PROD QTY VALUE ORDERQTY ORDERVALUE
> AAA 999 1 4 7 10
> BBB 888 2 5 8 11
> CCC 777 3 6 9 12
>
> How do I make it look at a thrid criteria of the date in row 1. Or perhaps
> even a fourth criteria of PROD in column B?
>
> Thanks,
>
> Esther

[EstherJ]

It works brilliantly. Thanks

"Domenic" wrote:

> Try the following formulas, which need to be confirmed with
> CONTROL+SHIFT+ENTER, not just ENTER...
>
> > How do I make it look at a thrid criteria of the date in row 1.
>
> =INDEX($C$3:$F$5,MATCH(H2,$A$3:$A$5,0),MATCH(1,($C$1:$F$1=J2)*($C$2:$F$2=
> K2),0))
>
> ....where H2 contains the 'Cust', J2 contains the 'Date', and K2 contains
> 'OrderQty'.
>
> > Or perhaps even a fourth criteria of PROD in column B?
>
> =INDEX($C$3:$F$5,MATCH(1,($A$3:$A$5=H2)*($B$3:$B$5=I2),0),MATCH(1,($C$1:$
> F$1=J2)*($C$2:$F$2=K2),0))
>
> ....where H2 contains the 'Cust', I2 contains the 'Prod', J2 contains the
> 'Date', and K2 contains 'OrderQty'.
>
> Hope this helps!
>
> In article <[email protected]>,
> EstherJ <[email protected]> wrote:
>
> > I have the following Index/Match formula which will return the value of 9 if
> > X17=CCC
> > X18=ORDERQTY
> >
> > =INDEX($C$3:$F$5,MATCH(X17,$A$3:$A$5,0),MATCH(X18,$C$2:$F$2,0))
> >
> > Apr-06 Apr-06 Apr-06 Apr-06
> > CUST PROD QTY VALUE ORDERQTY ORDERVALUE
> > AAA 999 1 4 7 10
> > BBB 888 2 5 8 11
> > CCC 777 3 6 9 12
> >
> > How do I make it look at a thrid criteria of the date in row 1. Or perhaps
> > even a fourth criteria of PROD in column B?
> >
> > Thanks,
> >
> > Esther
>