INDEX MATCH

  • Thread starter Thread starter Nicky
  • Start date Start date
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..

Thank you in advance.

Nicky
 
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..

Thank you in advance.

Nicky
Click to expand...


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))
 
try
=sumproduct((orders!a2:a22=c3)*(orders!b2:b22=c5)*(orders!c2:c22=a8)*orders!d2:d22)
 
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))
Click to expand...


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


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