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

#### Pikkle

##### New Member
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.

#### acw

##### MrExcel MVP
Hi

=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
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
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
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
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)
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
3
4
5
6
 Owing

This is the Sheet i am matching the data to:
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
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
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
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

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

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