INDEX-MATCH function return '0' if search criteria not found

Hi all,

The company I work for stores stock with varying numbers of different items frequently being brought in and sent out. To keep track of the daily activity of this stock I made a login sheet into which someone simply enters the date an item/s was moved, a description of the item/s (from a drop down list) and the quantity moved either entered into an 'In' or an 'Out' column. I also have a separate Excel workbook that keeps track of how many of each item we have in at any one time with individual worksheets for each month to make it easier to invoice.

I'm trying to use the INDEX-MATCH function (one for each day for each stored item) to search the login sheet firstly for a specified date and secondly for a specified description, and then finally display the figure shown in either the 'In' column (there will also be a separate formula displaying the figure from the 'Out' column).

At the moment I've got this;

{=INDEX('April 2012'!$F$4:$F$249,MATCH(1,('April 2012'!$C$4:$C$249=E26)*('April 2012'!$E$4:$E$249=A26),0))}

This seems to work but only if there is a date and description that match what the formula is searching for, if not the cell returns #N/A, but if this is the case I need the cell to equal '0' so that it doesn't mess up other formulas. Is this possible with a tweak or will I need a completely different formula to get this to work?

(In the example I've shown above the formula is searching a sheet in the same workbook but once I've got it working the sheet will be moved to a different workbook.)

Many thanks,

Ben

Check out IFERROR().

Pauley

This worked great, thanks very much Pauley! I love it when there's a simple solution. There's been a few other issues but so far everything's been an easy fix luckily.

Thanks again,

Ben