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

pedie

Well-known Member
Joined
Apr 28, 2010
Messages
3,875
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!:eek:
 

Excel Facts

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.
Try

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

=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)))
 
Upvote 0
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.
 
Upvote 0
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!:eek:
 
Upvote 0
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
 
Upvote 0
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?
 
Upvote 0
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.
 
Upvote 0
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>
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,255
Members
448,556
Latest member
peterhess2002

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Back
Top