VLOOKUP #N/A

[RICK_D]

I am working with a VLOOKUP formula. How can I subsitute the #N/A into a 0?

I don't want the results to say #N/A if it does not find the value. I want it to show 0.

Any help would be appreciated!!
Thanks in advanced!!

[Aladin Akyurek]

On 2002-03-22 11:53, RICK_D wrote:
I am working with a VLOOKUP formula. How can I subsitute the #N/A into a 0?

I don't want the results to say #N/A if it does not find the value. I want it to show 0.

Any help would be appreciated!!
Thanks in advanced!!

If your VLOOKUP formula looks like

=VLOOKUP(A1,$E$2:$G$40,2,FALSE)

use:

=IF(COUNTIF($E$2:$E$40,A2),VLOOKUP(A1,$E$2:$G$40,2,0),0)

If your VLOOKUP formula looks like

=VLOOKUP(A1,$E$2:$G$40,2,TRUE)

or

If your VLOOKUP formula looks like

=VLOOKUP(A1,$E$2:$G$40,2)

use:

=IF(ISNUMBER(MATCH(A2,$E$2:$E$40)),=VLOOKUP(A1,$E$2:$G$40,2),0)

[Dave Patton]

What formula are you using?

Aladin made excellent recommendations just a little earlier.

- slightly edited -

=IF(AND(LEN(A2),COUNTIF(XXXX,A2)),VLOOKUP(A2,XXXXY,2,0),0)

In English, if A2 is blank and A2 is not in range named XXXX, 0.

Otherwise lookup A2 in Database XXXXY

Credit to Aladin.
Please read his comments in Thread(s) on
Vlookup.





Aladin

[dantb]

Or if you dont want to supress your '0's on your worksheet, maby something like this. =IF(COUNTIF($E$2:$E$40,A2),VLOOKUP(A2,$E$2:$G$40,2,0)," ")

[Mark W.]

On 2002-03-22 11:53, RICK_D wrote:
I am working with a VLOOKUP formula. How can I subsitute the #N/A into a 0?

I don't want the results to say #N/A if it does not find the value. I want it to show 0.

Any help would be appreciated!!
Thanks in advanced!!

Return your VLOOKUP results to a hidden column and use an IF function to test for presence of #N/A.

=IF(ISNA(A2),0,A2)