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

[Pikkle]

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.

 

[PaddyD]

Hi Michael,

Constructions of the form:

=if(iserror(expression),something,expression)

...should be avoided, partly because it is inefficient to run the expression twice - usually, you can check precisely for the driver of the error condition, but mostly because iserror() masks all errors, including syntactic ones in formula construction:

=if(iserror(vlokup(),0,vlookup())

...is not the sort error you want to mask. see:


http://www.mrexcel.com/board2/viewtopic.php?t=62102

for some alternatives - at the very least you should use isna() as the check.

 

[Pikkle]

Hi Tony,

Your calculation works wonders. Thankyou very much for your help. I have not used countif before, i will now research it to find out why it works. Thankyou again for spending a lot of time help my the last couple of days.

Hi Micheal,

I tried your calculation to see what happened and it come up with a 0 but it changed the calculations which were working to 0 as well, thankyou for your help though. As you may see above my specific problem has been fixed.

Thanks All
Pikkle