# Getting rid of #N/A

#### neospec

##### New Member
I have a formula, but when it is unable to retrieve information it gives a #N/A error.

Is there anyway to change this error to \$0.00 or to get rid of it completely?

### Excel Facts

Does the VLOOKUP table have to be sorted?
No! when you are using an exact match, the VLOOKUP table can be in any order. Best-selling items at the top is actually the best.

#### sand112281

##### Board Regular
use an if statement. =if(isnumber(a1)=true,a1,0)

put your formula as a1 or some equivalent.

#### Jonmo1

##### MrExcel MVP
=IF(ISNA(VLOOKUP(A1,B1:C20,2,FALSE))=TRUE,"",VLOOKUP(A1,B1:C20,2,FALSE))

Modify the Vlookup functions to suit your needs. This will result in blank if result of vlookup is #N/A. You can also put \$0.00 inside the Quotation marks if you want it to display \$0.00 when result is #N/A.

#### neospec

##### New Member

My formula is currently

=INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH(\$B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2)

So, how would I implement

=IF(ISNA(VLOOKUP(A1,B1:C20,2,FALSE))=TRUE,"",VLOOKUP(A1,B1:C20,2,FALSE))

#### sand112281

##### Board Regular
you dont need the vlookups

assuming that the result of the formula is a number.

=if(isna(INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH(\$B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2))=true,0,INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH(\$B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2))

you could replace the 0 with "" depending on what you want there.

#### Jonmo1

##### MrExcel MVP

I found 2 ways. I'm pretty new here, I'm sure somebody else will find a better way.

Method 1, (and the easier way if you ask me)

Add a collumn next to the collumn with your formula and use this.

Collumn B
=IF(ISNA(A1)=TRUE,"\$0.00",A1)

Then if you want, you can hide the collumn that contains the main formula.

Method 2 seems far too extreme to me (looks like sand112281 already did this one)

Applying the isna function to your existing formula would make the formula extremely long. But here's how you would do it. Just insert your formula (without the equal sign) in both places where I put a vlookup formula. Something like this :

=IF(ISNA(INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH(\$B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2))=TRUE,"\$0.00",INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH(\$B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2))

#### NBVC

##### Well-known Member
I have a formula, but when it is unable to retrieve information it gives a #N/A error.

Is there anyway to change this error to \$0.00 or to get rid of it completely?

Try:

=IF(ISNUMBER(MATCH(\$B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2),INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH(\$B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2),0)

and format the cell accordingly

#### neospec

##### New Member
Ok, tried the last one, and got some whacky incorrect values.

#### NBVC

##### Well-known Member
Should've worked. But you could take out the +2 from the Match condition after the IF()...

=IF(ISNUMBER(MATCH(\$B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)),INDEX(INDIRECT("'[2006SalesReport.xls]January06'!I1:i2000"),MATCH(\$B21,INDIRECT("'[2006SalesReport.xls]January06'!C1:C2000"),0)+2),0)

Replies
2
Views
71
Replies
2
Views
48
Replies
2
Views
30
Replies
8
Views
122
Replies
2
Views
30