# Need a zero instead of #N/A

#### Tarvalon

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

### Excel Facts

Create a chart in one keystroke
Select the data and press Alt+F1 to insert a default chart. You can change the default chart to any chart type
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)

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!

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

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!!!

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.

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

Replies
1
Views
40
Replies
6
Views
212
Replies
7
Views
203
Replies
5
Views
85
Replies
1
Views
161

1,203,242
Messages
6,054,359
Members
444,718
Latest member
r0nster

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back