Need a zero instead of #N/A

[Tarvalon]

My vlookup:

=VLOOKUP([@Date],'H:\Haydn\[Mandy_Mill_Uptime.xls]Mandy_Mill_Uptime'!$A$1:$G$65536,4,FALSE)

is returning N/A. How can I make it a zero if it's N/A?

Thanks,

Tarv.

 

[BenMiller]

For Excel 2003, =IF(ISNA(VLOOKUP([@Date],'H:\Haydn\[Mandy_Mill_Uptime.xls]Mandy_Mill_Uptime'!$A$1:$G$65536,4,FALSE)),0,=VLOOKUP([@Date],'H:\Haydn\[Mandy_Mill_Uptime.xls]Mandy_Mill_Uptime'!$A$1:$G$65536,4,FALSE))

For 2007 and up, =IFERROR(=VLOOKUP([@Date],'H:\Haydn\[Mandy_Mill_Uptime.xls]Mandy_Mill_Uptime'!$A$1:$G$65536,4,FALSE),0)

 

[TKB]

I think this will work:

=IF(VLOOKUP([@Date],'H:\Haydn\[Mandy_Mill_Uptime.xls]Mandy_Mill_Uptime'!$A$1:$G$65536,4,FALSE)>0, =VLOOKUP([@Date],'H:\Haydn\[Mandy_Mill_Uptime.xls]Mandy_Mill_Uptime'!$A$1:$G$65536,4,FALSE), 0)

This says if the value found through VLOOKUP is > 0, it will put that value in the cell, otherwise it will read as 0.

Hope that works!

 

[sitewolf]

in other words, you can make it say about anything you want using an if statement....you could have it say "I'm not a perfectionist; I'm just perfect." if you wanted to (you'd just put quotes around it if it's text)

curious why you guys tend to use A1:G65536 rather than just A:G though

 

[Tarvalon]

curious why you guys tend to use A1:G65536 rather than just A:G though

I only remember selecting the columns. Don't know why it says G65536. Strange!

But THANKS...it worked!!!

 

[Tarvalon]

Now I have this:

=SUMIF(Mandy_Daily_Job_Yield.xls!$A:$A,[@Date],Mandy_Daily_Job_Yield.xls!$G:$G)

It only works if the Mandy_Daily_Job_Yield is open.

 

[BenMiller]

No problem Glad I could help Appreciate the feedback!

And I just copied the existing formula and wrapped an IFERROR around it; that's why I didn't do A:G