Omitting #N/A

[Todd]

I have a Vlookup formula that looks up a date on another
worksheet if the date is there. The date may or may not
be entered each time therefore the #N/A is returned as the
value when the date is not there. The cell with the
Vlookup formula is linked to another worksheet with a
basic sum function which will not work because of the #N/A
that is on the Vlookup spreadsheet. Is there anyway to
keep the #N/A from populating that cell with the Vlookup
formula? Thanks for the help.

Todd

 

[Stuart Farr]

Yes, use the ISERROR() function.

e.g. IF(ISERROR(VLOOKUP(.....)=TRUE),0,VLOOKUP(....)).

 

[Alan]

You could try this as a format, change the cell references obviously. Change
"Not Found" to 0 or "" or whatever,

Basically if the the VLOOKUP returns #N/A then the 'Not found' is returned

=IF(ISNA(VLOOKUP(C1,A1:B13,2,FALSE)),"Not Found",VLOOKUP(C1,A1:B13,2,FALSE))

Watch for text wrap!

Regards Alan.

 

[Peo Sjoblom]

You could either use sumif instead on the other worksheet

=SUMIF(Sum_Range,"<>#N/A")

will disregards the #N/A, I would probably use that myself since I
consider the errors good info. If not use something like

=IF(A1="",0,VLOOKUP(A1,.........))

 

[Lynn]

What I usually do is, I'll copy the cell and paste value
over it to remove the vlookup formula. Then I'll use
Find/Replace (Ctrl+H) to find #N/A and replace with null
value. That is, leave the Replace With field blank.
Sounds tedious but once you get the hang of it, it's
peanuts.

Is there any better solutions around?