# Index/Match Multiple Criteria

G

#### Guest

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

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!

It works brilliantly. Thanks

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

=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'.

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