Force #N/A to 0

[SandyUK]

I have a formula which use IF and VLOOKUP that refers to a linked worksheet and depending on the data in the linked worksheet will return a value or #N/A because the VLOOKUP value is not present in the linked worksheet.

I am trying to get the formula to return a 0 rather than N/A when the LOOKUP value is not present in the linked worksheet

=IF(A24="","0",(VLOOKUP(A24,'Y:\Steve\Planning\MSP''S\RED MRP\[RED MRPS JAN.xls]2005'!$1:$65536,27,FALSE)))*(VLOOKUP(A24,Costing,11,FALSE))

As you can see if the value A24 is not present in

'Y:\Steve\Planning\MSP''S\RED MRP\[RED MRPS JAN.xls]2005'

Then #N/A is returned and I need it to return a 0

Any help would be appreciated.

[theDude]

Sandy,

Try this:

=IF(ISERROR(VLOOKUP(A24,'Y:\Steve\Planning\MSP''S\RED MRP\[RED MRPS JAN.xls]2005'!$1:$65536,27,FALSE)), 0, VLOOKUP(A24,'Y:\Steve\Planning\MSP''S\RED MRP\[RED MRPS JAN.xls]2005'!$1:$65536,27,FALSE)) * (VLOOKUP(A24,Costing,11,FALSE))

Hope this helps,
theDude

[SandyUK]

Thanks a lot

I tried something similar =IF(ISNA(YourFormula),0,YourFormula)

Though i can see the advantage of your way i.e. that it will enter a 0 for all errors.

The only problem i have now is that the formula is getting very long and i need to update the file path to the relevant data worksheet on a weekly basis for a large amount of rows. I tried copying the file path into a cell on another sheet within my workbook and then copying this cell into the formula e.g.

=IF(ISNA(VLOOKUP(A23,Data!$B$6,27,FALSE))*(VLOOKUP(A23,Costing,11,FALSE)),0,VLOOKUP(A23,Data!$B$6,27,FALSE))*(VLOOKUP(A23,Costing,11,FALSE)))

Where Data!$B$6 contains the file path to the workbook with the data but i am not achieving any success.

I am going to post this as a question but thought i would let you know i am achieveing some success and thank you for the help.