Match returns #N/A, want it to be 0

Pikkle

New Member
Joined
Sep 12, 2005
Messages
13
Hi All,

I am combining two sheets together. The first sheet is data we complete in our office for pallets we have returned. The Second sheet is confirmation from the pallet controller on how many he recieved. I need to conbine them.

Both sheets use the same reference number. Therefore i have created this equation.

=index('Data Sent Back'!$A$1:$I$5000,match(Owing!$C4,'Data Sent Back'!$E$1:$E$5000,0)1)

This calculation works for the matching figures but the ones that do not match return an N/A. I want the N/A to show as 0 to show that the information has not come from the other company and the pallets are still outstanding.

I hope this all makes sense to you, I am starting to loose it. :eek:
 

Excel Facts

Fastest way to copy a worksheet?
Hold down the Ctrl key while dragging tab for Sheet1 to the right. Excel will make a copy of the worksheet.
Hi

How about using SUMPRODUCT

=sumproduct(--('data sent back'!$e$1:$e$5000=$c4),('data sent back'!$a$1:$a$5000))

I think I have the columns the right way to match the reference number....

Tony
 
Upvote 0
It works great for turning the N/A into a 0 but the calculation does not work for the items that were already matched correctly. I don't understand the -- bit?
 
Upvote 0
Hi

1) What does it do to the already matched items? I would have thought it would bring back the same number? Perhaps some example data and outputs...

2) the -- converts the boolean result (true / false) to numeric (1 or 0) to work in the multiplication.

Tony
 
Upvote 0
Hi,

I have no idea how to copy the spreadsheet onto this reply, sorry.

1) the calculation i have set up matches the data in C4 on the open sheet to data in column E on the other sheet then gives me the data in column A of the matched row. if there are no matches it shows as #N/A

Pikkle
 
Upvote 0
Thanks Tony,

That's a great tool. You have changed my queries for ever.

Here is the sheet where the working out is, the area under "Chep data" is the areas i have matched to the other sheet (below this)
Grant's Spreadsheet - html.xls
ABCDEFGHIJKLMNOPQR
1SENDING CUSTOMERRECEIVING STORECHEP DATA
2Data Week EndingSending StoreCUST REF (Con Note No)BUSINESSCHEP ACCOUNTEFFECTIVE DATECHEP ACCOUNTSTORE NUMBERLOCATION (Suburb/Town)CHEP QTYOTHER QTYCML Docket #Transfer DescriptionDescription DebitChep QtyChep Data Week EndingPallet Difference
313/10/2005A0477653368364/10/200500102101#N/A#N/A#N/A#N/A#N/A#N/A
46/10/2005EM000004933683620/09/20050010592012-TF-9060140Transf-Off1 - CHEP Pallets206/10/20050
56/10/2005EM000050233683622/09/20050010591712-TF-9060139Transf-Off1 - CHEP Pallets176/10/20050
66/10/2005EM000050633683626/09/20050010592442-TF-9060134Transf-Off1 - CHEP Pallets246/10/20050
Owing


This is the Sheet i am matching the data to:
Grant's Spreadsheet - html.xls
ABCDEFGHI
1Docket NumberDateSending CustomerNameCML DocketTrans. DescriptionDescriptionDebitWeek Ending
22-TF-906014020/09/2005336836EM0000049Transf-Off1 - CHEP Pallets206/10/2005
32-TF-906013922/09/2005336836EM0000502Transf-Off1 - CHEP Pallets176/10/2005
42-TF-906013426/09/2005336836EM0000506Transf-Off1 - CHEP Pallets246/10/2005
Data Sent Back


I hope this helps you understand better.

Pikkle
 
Upvote 0
Hi

Try putting this in Owing!M3

Code:
=IF(COUNTIF('Data Sent Back'!$E$2:$E$4793,Owing!C3),INDEX('Data Sent Back'!$A$2:$I$4793,MATCH(Owing!$C3,'Data Sent Back'!$E$2:$E$4793,0),1),"")

If this works, then you can adapt for the other associated columns.


Tony
 
Upvote 0
Hi Tony,

I will work on this later this afternoon, Today is pay day and as i am the pay clerk, i have to do my job first. I'd much rather play though. Thankyou for your time. I'll get back to you later on whether it works.

Pikkle
 
Upvote 0
Pikkle said:
=index('Data Sent Back'!$A$1:$I$5000,match(Owing!$C4,'Data Sent Back'!$E$1:$E$5000,0)1)

This calculation works for the matching figures but the ones that do not match return an N/A. I want the N/A to show as 0 to show that the information has not come from the other company and the pallets are still outstanding.

I had the very same issue earlier today.
This worked for me:

=if(IsError(index('Data Sent Back'!$A$1:$I$5000,match(Owing!$C4,'Data Sent Back'!$E$1:$E$5000,0)1)),0,index('Data Sent Back'!$A$1:$I$5000,match(Owing!$C4,'Data Sent Back'!$E$1:$E$5000,0)1))

That should work!

Michael
 
Upvote 0

Forum statistics

Threads
1,213,535
Messages
6,114,198
Members
448,554
Latest member
Gleisner2

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top