Excel Question

[JimBeam]

OK so I have a VLOOKUP formula that, by default, returns " #N/A " when it does not find the value that it's looking up.

The problem is I'm calculating the entire column and that formula will not return the value I'm looking for because it won't sum the " #N/A ".

Is there a way to make either the original VLOOKUP formula return a value of " 0 " when it does not find a match or have the SUM formula ignore and/or treat the " #N/A " as a zero ?

[biggestmexi]

i am not exactly sure but have you tried formatting that cell into a number one?

MAYBE that might work.

[badorties]

OK so I have a VLOOKUP formula that, by default, returns " #N/A " when it does not find the value that it's looking up.

The problem is I'm calculating the entire column and that formula will not return the value I'm looking for because it won't sum the " #N/A ".

Is there a way to make either the original VLOOKUP formula return a value of " 0 " when it does not find a match or have the SUM formula ignore and/or treat the " #N/A " as a zero ?

i have such an undying love for the VLOOKUP function ... for #N/A, i usually kill the formulas and leave the text or values, and use [Replace] to submit a dash for text values and a zero for numeric values

but i found this:

Question: In Excel, I'm using the VLookup function to return a value. I want to sum the results of the VLookup, but I can't because the VLookup returns a #N/A error if no match is found. How can I sum the results when there are instances of #N/A in it?

Answer: To perform mathematical operations on your VLookup results, you need to replace the #N/A error with a 0 value (or something similar). This can be done with a formula that utilizes a combination of the VLookup function, IF function, and ISNA function.

or

Stop The #N/A! Error in VLOOKUP

The single best and most efficient way is to do this is;
1) Add your lookup formula to a spare column (e.g Column "A") and allow the #N/A! to happen.

2) Now reference these cells from the required cells like this;

=IF(ISNA(A1),0,A1)

3) Hide Column "A" by selecting it and going to Format>Column>Hide

The second most efficient is probably like this;
Instead of;

=VLOOKUP("Dog",A1:D100,2,False)

Use:

=IF(COUNTIF(A1:A100,"Dog"),VLOOKUP("Dog",A1:D100,2,FALSE),0)
The least efficient method is like;
=IF(ISNA(VLOOKUP("Dog",A1:D100,2,FALSE)),0,VLOOKUP("Dog",A1:D100,2,FALSE))
If you don't like the Zeros showing you can hide them via Tools>Options>View - Zero values. Or, cell-by-cell with a Custom Format like: General;-General;

[biggestmexi]

=VLOOKUP("Dog",A1100, 2 ,False)

Use:

=IF(COUNTIF(A1:A100,"Dog"),VLOOKUP("Dog",A1100, 2 ,FALSE),0)
The least efficient method is like;
=IF(ISNA(VLOOKUP("Dog",A1100, 2 ,FALSE)),0,VLOOKUP("Dog",A1100, 2 ,FALSE))

i put in some spaces before and after the 2.

[IMSlacker]

I usually just filter for #N/A on the vlookup column, and just replace the #N/A's with zeroes. But the IF statement is a better fix.

[JimBeam]

I was doing the manual changes but I figured there was, or at least should be, an easier way.

Thanks for all the input.

[angrymissy]

I use VLOOKUP at lot @ work, I always just find/replace w/ 0

[Bob Impact]

dammit i had an answer and its already answered, yeah IF(ISNA()) Optionally IF(ISERR()) if you get #values.

[badorties]

i have data exported from access 2000 to excel 2000, and each cell's data starts with an apostrophe ' (i remember from my lotus 123 days that was a justification marker)

the trouble is the nemeric data isn't recognized, and i have to work each column to fix them ... any way of making a clean sweep of the whole file ...?

[JPMNICK]

i have data exported from access 2000 to excel 2000, and each cell's data starts with an apostrophe ' (i remember from my lotus 123 days that was a justification marker)

the trouble is the nemeric data isn't recognized, and i have to work each column to fix them ... any way of making a clean sweep of the whole file ...?

just do a Find/replace. look for the ' in the find and replace it with nothing

[badorties]

i tried that ... it doesn't recognize the ' as a character

i also tried copy/pasting (value only) the data into a new sheet, but the data still wouldn't cooperate

[JPMNICK]

i tried that ... it doesn't recognize the ' as a character

i also tried copy/pasting (value only) the data into a new sheet, but the data still wouldn't cooperate

can you Ctrl+X the charector so you get the exact one and then paste it into the Find field?

[badorties]

same result ... i just have to roll up the proverbial sleeves and the 2 dozen columns

thanks for the help

[badorties]

wow ... a double post

[angrymissy]

There is a program for Excel called ASAP Utilities (it's free). It does a lot of that pain in the ass stuff people can't figure out (like removing the ').

http://www.asap-utilities.com/