INDEX MATCH
 

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),MAT CH(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

INDEX MATCH
 

Nicky wrote:
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),MAT CH(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:$A1 0)*
(C$5=Orders!$B2:$B10)*($A8=Orders!$C2:$C10))

INDEX MATCH
 

try
=sumproduct((orders!a2:a22=c3)*(orders!b2:b22=c5)* (orders!c2:c22=a8)*orders!d2:d22)
--
Don Guillett
Microsoft MVP Excel
SalesAid Software

"Nicky" wrote in message
...
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),MAT CH(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

INDEX MATCH
 

Glenn wrote:
Nicky wrote:
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),MAT CH(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:$A1 0)*
(C$5=Orders!$B2:$B10)*($A8=Orders!$C2:$C10))


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

INDEX MATCH
 

Try this:
=INDEX(Orders!D1:D7,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--

--
Ryan---
If this information was helpful, please indicate this by clicking ''Yes''.


"Nicky" wrote:

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),MAT CH(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