# how do we remove both #N/A error and 0 in Vlookup, thank you.

#### pedie

##### Well-known Member
I have a table from where i do a vlookup.
when i try to remove by 0 from appearing when there is no value in table
=IF(ISBLANK(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)),"",(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)))

#n/A appears
and when i use
=IF(ISNA(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)),"",(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)))

when the cell is blank 0 appears...

Is there a formula to avoid getting any of this error/ result and get blank with there is no value in table

Thanks again!

### Excel Facts

Will the fill handle fill 1, 2, 3?
Yes! Type 1 in a cell. Hold down Ctrl while you drag the fill handle.

#### jasonb75

##### Well-known Member
Try

=IF(ISNA(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2,0)),"",(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2,0)))

#### PA HS Teacher

##### Well-known Member

=IF(ISNA(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)),"",IF(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)=0,"",VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)))

#### xenou

##### MrExcel MVP
Is there a formula to avoid getting any of this error/ result and get blank with there is no value in table

At times a custom number format also works to "hide" such values. Or conditional formatting (using white font). I often use the former to hide zeros, and the latter to "hide" error values - but white font makes a ghostly appearance when you select the cells.

#### JHASDOIT

##### New Member

try this formula..

=IF(C3<>"",(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)),"")

#### tusharm

##### MrExcel MVP
If you are using 2003 or earlier use PA HS Teacher's approach. If 2007 or later use =IFERROR(IF(VLOOKUP(I14,\$N\$4:\$O\$18,2,FALSE)="","",VLOOKUP(I14,\$N\$4:\$O\$18,2,FALSE)),"")

Of course, you will have to adjust the ranges for your own setup.

I have a table from where i do a vlookup.
when i try to remove by 0 from appearing when there is no value in table
=IF(ISBLANK(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)),"",(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)))

#n/A appears
and when i use
=IF(ISNA(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)),"",(VLOOKUP(\$C\$3,CompletedTrainingsonly!\$A\$20:\$V\$35,2)))

when the cell is blank 0 appears...

Is there a formula to avoid getting any of this error/ result and get blank with there is no value in table

Thanks again!

#### kjacw

##### Board Regular

As you can see from my report card spreadsheet that I am building below, I am using something like a Vlookup and at times I cannot show these zero's.

1. I would like to know specifically how can I use this formula to hide my zero's on the report card using the Conditional Formatting by making the text white if it is a '0'. I am using Excel 2007 and will this work on 2003 if I transferred it using a thumb drive.

2. I would like to try also to use a formula to make the '0' not to appear because as you see below that there are some sections on the report card that just are not filled in because the grades of the student passing and it is not necessary to take other tests. So what formula can be used with my index formula here?
=INDEX(dataa,MATCH(N810,lookaup,0),2)

Excel Workbook
ABCDEFGHIJKLMN
808Ana MariaFirst Semester
809Subjects1st2nd3rd4thAV1EX1AS1CT1AC1ET1AE1FA1
810Bible98000250170900Ana MariaBibleFirst Semester
811Mathematics80000200140700100Ana MariaMathematicsFirst Semester
Report Card

##### MrExcel MVP
A808:

=IF('1 Per'!A17="","",'1 Per'!A17)

B808 and similar...

=INDEX(dataa,MATCH(N810,lookaup,0),2)

Rich (BB code):
=IF(ISNUMBER(MATCH(N810,lookaup,0)),
IF(INDEX(dataa,MATCH(N810,lookaup,0),2)="","",
INDEX(dataa,MATCH(N810,lookaup,0),2)),"")

PS. Why not lookupa instead of lookaup?

#### Fazza

##### MrExcel MVP
It seems like the sheet shown is a report from separate data. If so, perhaps non-formula approaches (like a pivot table) are suitable? With such approaches, there can be a null in the cells for which there is no data. So, not a string of "", not a zero, but nothing at all.

#### kjacw

##### Board Regular
Upon checking my spreadsheet I see that I have a problem in the Quiz section. Let me explain:

If I enter three Quiz grades in (as seen) AE374= 7; AF374= 8 ; AG374= 6 and the count blank in AJ374 = 3 and the division takes place according to the formula that is set in AI374 but when I remove a grade from AH374 (as seen) then the count blank remains on 3 which was the previous number of Quiz grades and it divides the 2 numbers by 3 and there are only two blanks. Is there a solution for this?
2 Per

<table style="font-family: Calibri,Arial; font-size: 11pt; background-color: rgb(255, 255, 255); padding-left: 2pt; padding-right: 2pt;" border="1" cellpadding="0" cellspacing="0"> <colgroup><col style="font-weight: bold; width: 30px;"><col style="width: 21px;"><col style="width: 18px;"><col style="width: 20px;"><col style="width: 23px;"><col style="width: 40px;"><col style="width: 27px;"></colgroup><tbody><tr style="background-color: rgb(202, 202, 202); text-align: center; font-weight: bold; font-size: 8pt;"><td>
</td><td>AE</td><td>AF</td><td>AG</td><td>AH</td><td>AI</td><td>AJ</td></tr><tr style="height: 22px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">371</td><td colspan="5" style="font-family: Maiandra GD; font-size: 14pt; text-align: center;">Quiz</td><td style="font-family: Maiandra GD; font-size: 14pt;">
</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">372</td><td colspan="5" style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">10%</td><td style="font-family: Maiandra GD; font-size: 12pt;">
</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">373</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">1</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">2</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">3</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">4</td><td style="color: rgb(0, 0, 255); font-weight: bold; font-family: Maiandra GD; font-size: 12pt; text-align: center;">T</td><td style="color: rgb(0, 0, 255); font-weight: bold; font-family: Maiandra GD; font-size: 12pt; text-align: center;">#</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">374</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">7</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">8</td><td style="font-family: Maiandra GD; font-size: 12pt;">
</td><td style="font-family: Maiandra GD; font-size: 12pt;">
</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">5</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">3</td></tr><tr style="height: 19px;"><td style="font-size: 8pt; background-color: rgb(202, 202, 202); text-align: center;">375</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">8</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">8</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: right;">5</td><td style="font-family: Maiandra GD; font-size: 12pt;">
</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">7</td><td style="font-family: Maiandra GD; font-size: 12pt; text-align: center;">3</td></tr></tbody></table>
<table style="font-family: Arial; font-size: 10pt; border-style: groove; border-color: rgb(0, 255, 0); background-color: rgb(255, 252, 249); color: rgb(0, 0, 0);"><tbody><tr><td>Spreadsheet Formulas</td></tr><tr><td><table style="font-family: Arial; font-size: 9pt;" border="1" cellpadding="2" cellspacing="0"><tbody><tr style="background-color: rgb(202, 202, 202); font-size: 10pt;"><td>Cell</td><td>Formula</td></tr><tr><td>AI374</td><td>=SUM(AE374:AH374)/AJ374</td></tr><tr><td>AJ374</td><td>=4-COUNTBLANK(AE374:AH374)</td></tr><tr><td>AI375</td><td>=SUM(AE375:AH375)/AJ375</td></tr><tr><td>AJ375</td><td>=4-COUNTBLANK(AE375:AH375)</td></tr></tbody></table></td></tr></tbody></table>

Replies
9
Views
73
Replies
2
Views
73
Replies
24
Views
290
Replies
1
Views
653
Replies
3
Views
294

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

1,163,496
Messages
5,832,034
Members
430,105
Latest member
acemovingco

### 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