Thanks:  0
Likes:  0

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

2. 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!!
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)

3. What formula are you using?

- 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

Vlookup.

4. 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)," ")

5. 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!!
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)

## User Tag List

#### Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•