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

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
Joined
Dec 30, 2008
Messages
14,761
Office Version
  1. 365
Platform
  1. Windows
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
Joined
Jul 17, 2004
Messages
2,838
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)))
 

xenou

MrExcel MVP
Joined
Mar 2, 2007
Messages
16,836
Office Version
  1. 2019
Platform
  1. Windows
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
Joined
Jul 23, 2010
Messages
2

ADVERTISEMENT

try this formula..

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

tusharm

MrExcel MVP
Joined
May 28, 2002
Messages
11,029
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:
 

kjacw

Board Regular
Joined
Jun 29, 2010
Messages
144

ADVERTISEMENT

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
 

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,210
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
Joined
May 17, 2006
Messages
9,368
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
Joined
Jun 29, 2010
Messages
144
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>
 
Master Excel Bundle

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.

Forum statistics

Threads
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.
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
Top