# INDEX MATCH

N

#### Nicky

I am trying to get the values for a delivery note. I have all the data in a
seperate worksheet called ORDERS as below

Date Part No Depot Qty
11/11/09 F13624 4 200
12/11/09 F13625 5 300

The formula I have used is:

=INDEX(ORDERS!\$D:\$D,MATCH(\$C\$3,ORDERS!\$A:\$A,0),MATCH(C\$5,ORDERS!\$B:\$B,0),MATCH(\$A8,ORDERS!\$C:\$C,0))

This keeps returning a #N/A error

On my delivery note

C3 holds the date
C5 holds the part no
A8 holds the depot

I want it to match all 3 criteria and return the qty.

Get again I need the experts, its probably something really silly..

Nicky

G

#### Glenn

Nicky said:
I am trying to get the values for a delivery note. I have all the data in a
seperate worksheet called ORDERS as below

Date Part No Depot Qty
11/11/09 F13624 4 200
12/11/09 F13625 5 300

The formula I have used is:

=INDEX(ORDERS!\$D:\$D,MATCH(\$C\$3,ORDERS!\$A:\$A,0),MATCH(C\$5,ORDERS!\$B:\$B,0),MATCH(\$A8,ORDERS!\$C:\$C,0))

This keeps returning a #N/A error

On my delivery note

C3 holds the date
C5 holds the part no
A8 holds the depot

I want it to match all 3 criteria and return the qty.

Get again I need the experts, its probably something really silly..

Nicky

Adjust the ranges to match your data. You can't reference the whole column with
SUMPRODUCT() unless you are using Excel 2007.

=SUMPRODUCT((Orders!\$D2:\$D10)*(\$C\$3=Orders!\$A2:\$A10)*
(C\$5=Orders!\$B2:\$B10)*(\$A8=Orders!\$C2:\$C10))

D

#### Don Guillett

try
=sumproduct((orders!a2:a22=c3)*(orders!b2:b22=c5)*(orders!c2:c22=a8)*orders!d2:d22)

G

#### Glenn

Glenn said:
Adjust the ranges to match your data. You can't reference the whole
column with SUMPRODUCT() unless you are using Excel 2007.

=SUMPRODUCT((Orders!\$D2:\$D10)*(\$C\$3=Orders!\$A2:\$A10)*
(C\$5=Orders!\$B2:\$B10)*(\$A8=Orders!\$C2:\$C10))

Or this array formula (commit with CTRL+SHIFT+ENTER):

=INDEX(Orders!D110,MATCH(C3&C5&A8,Orders!A1:A10&Orders!B1:B10&Orders!C1:C10,0))