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:
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Pikkle

New Member
Joined
Sep 12, 2005
Messages
13
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?
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Pikkle

New Member
Joined
Sep 12, 2005
Messages
13
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
 

Pikkle

New Member
Joined
Sep 12, 2005
Messages
13
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)

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
 

acw

MrExcel MVP
Joined
Feb 13, 2004
Messages
4,814
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
 

Pikkle

New Member
Joined
Sep 12, 2005
Messages
13
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
 

daniels012

Well-known Member
Joined
Jan 13, 2005
Messages
5,219
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
 

Forum statistics

Threads
1,078,536
Messages
5,341,027
Members
399,412
Latest member
Bryanhj

Some videos you may like

This Week's Hot Topics

  • Problem with Radio Button's format control
    I am creating an employee evaluation template (a sample is below) Column A is the category Column B, C D, E and F will be ratings (unacceptable...
  • Last Display on userform to a Listbox
    [CODE=vba] lstdisplay.ColumnCount = 15 lstdisplay.RowSource = "A1:O600000" [/CODE] So when i do this it Displays everything on the sheet i am...
  • Rename and move files to a new location
    Dear all, I have an excel file with the following information. The actual file name is at column A but i want to rename it using the following...
  • Help with True/False Formula
    Hello! Am stumped how to fix this formula, in which my result returns 'True', but it should return False. =IF(AG2=True...
  • Clear extra characters from a provided range of cells
    Dear All, I have following code which gives me desired output to remove extra characters from a provided range. But it takes too much time when...
  • Help with Current and highest streaks
    Hi there, I've just joined the forum and this is my first post. I've already spent quite a bit of time searching the net and this forum for a...
Top