INDEX MATCH

[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

 

[Glenn]

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

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))

 

[Don Guillett]

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

 

[Glenn]

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))

 

[ryguy7272]

Try this
=INDEX(Orders!D17,MATCH(C3,Orders!A1:A7,MATCH(C5,Orders!B1:B7,MATCH(A8,Orders!C1:C7))))

This is a good resource:
http://www.contextures.com/xlFunctions03.html

HTH,
Ryan--