software

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-06Apr-06Apr-06Apr-06
CUSTPRODQTYVALUE ORDERQTYORDERVALUE
AAA99914710
BBB88825811
CCC77736912

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

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

gt; 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'.

gt; 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 gt;,
EstherJ gt; wrote:

gt; I have the following Index/Match formula which will return the value of 9 if
gt; X17=CCC
gt; X18=ORDERQTY
gt;
gt; =INDEX($C$3:$F$5,MATCH(X17,$A$3:$A$5,0),MATCH(X18, $C$2:$F$2,0))
gt;
gt; Apr-06Apr-06Apr-06Apr-06
gt; CUSTPRODQTYVALUE ORDERQTYORDERVALUE
gt; AAA99914710
gt; BBB88825811
gt; CCC77736912
gt;
gt; How do I make it look at a thrid criteria of the date in row 1. Or perhaps
gt; even a fourth criteria of PROD in column B?
gt;
gt; Thanks,
gt;
gt; Esther

It works brilliantly. Thanks

quot;Domenicquot; wrote:

gt; Try the following formulas, which need to be confirmed with
gt; CONTROL SHIFT ENTER, not just ENTER...
gt;
gt; gt; How do I make it look at a thrid criteria of the date in row 1.
gt;
gt; =INDEX($C$3:$F$5,MATCH(H2,$A$3:$A$5,0),MATCH(1,($C $1:$F$1=J2)*($C$2:$F$2=
gt; K2),0))
gt;
gt; ....where H2 contains the 'Cust', J2 contains the 'Date', and K2 contains
gt; 'OrderQty'.
gt;
gt; gt; Or perhaps even a fourth criteria of PROD in column B?
gt;
gt; =INDEX($C$3:$F$5,MATCH(1,($A$3:$A$5=H2)*($B$3:$B$5 =I2),0),MATCH(1,($C$1:$
gt; F$1=J2)*($C$2:$F$2=K2),0))
gt;
gt; ....where H2 contains the 'Cust', I2 contains the 'Prod', J2 contains the
gt; 'Date', and K2 contains 'OrderQty'.
gt;
gt; Hope this helps!
gt;
gt; In article gt;,
gt; EstherJ gt; wrote:
gt;
gt; gt; I have the following Index/Match formula which will return the value of 9 if
gt; gt; X17=CCC
gt; gt; X18=ORDERQTY
gt; gt;
gt; gt; =INDEX($C$3:$F$5,MATCH(X17,$A$3:$A$5,0),MATCH(X18, $C$2:$F$2,0))
gt; gt;
gt; gt; Apr-06Apr-06Apr-06Apr-06
gt; gt; CUSTPRODQTYVALUE ORDERQTYORDERVALUE
gt; gt; AAA99914710
gt; gt; BBB88825811
gt; gt; CCC77736912
gt; gt;
gt; gt; How do I make it look at a thrid criteria of the date in row 1. Or perhaps
gt; gt; even a fourth criteria of PROD in column B?
gt; gt;
gt; gt; Thanks,
gt; gt;
gt; gt; Esther
gt;