# how to disable #N/A

#### silverskye787

##### Board Regular
I have this following codes on cell G6

=VLOOKUP(G5,'P1'!K133:L135,2)

i want it to show an empty cell instead of #N/A if it returns 0 value...

need some urgent help here.. thanks

### Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.

#### mithu81

##### Board Regular
here you go.. this will show a 0 in the cell instead of n/a

=if(iserror(VLOOKUP(G5,'P1'!K133:L135,2),0,VLOOKUP(G5,'P1'!K133:L135,2))

#### mithu81

##### Board Regular
if you want to show a blank cell use this

=if(iserror(VLOOKUP(G5,'P1'!K133:L135,2),"",VLOOKUP(G5,'P1'!K133:L135,2))

#### silverskye787

##### Board Regular
there is still an error for this set of codes..

#### mithu81

##### Board Regular
=if(iserror(VLOOKUP(G5,'P1'!\$K\$133:\$L\$135,2,false),"",VLOOKUP(G5,'P1'!\$K\$133:\$L\$135,2,false)

try that

#### mithu81

##### Board Regular
=if(iserror(VLOOKUP(G5,'P1'!\$K\$133:\$L\$135,2,false),"",VLOOKUP(G5,'P1'!\$K\$133:\$L\$135,2,false))

try that

#### silverskye787

##### Board Regular
there is still an error..

it highlighted on "" this of the codes...

##### MrExcel MVP
I have this following codes on cell G6

=VLOOKUP(G5,'P1'!K133:L135,2)

i want it to show an empty cell instead of #N/A if it returns 0 value...

need some urgent help here.. thanks

Assuming that K133:L135 is sorted in ascending order on K...

=IF(VLOOKUP(G5,'P1'!\$K\$133:K\$135,1,1)=0,"",VLOOKUP(G5,'P1'!K133:L135,2,1))

#### silverskye787

##### Board Regular
there is still #N/A shown... i don't want anythin to be display when it return 0 value

#### mithu81

##### Board Regular
=if(iserror(VLOOKUP(G5,'P1'!\$K\$133:\$L\$135,2,false)),"",VLOOKUP(G5,'P1'!\$K\$133:\$L\$135,2,false))

that should work

Replies
2
Views
333
Replies
1
Views
53
Replies
26
Views
350
Replies
3
Views
207
Replies
7
Views
228

1,191,285
Messages
5,985,744
Members
439,979
Latest member
alekun86

### We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.

### Which adblocker are you using?

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

### Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

### Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back