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:
 

Some videos you may like

Excel Facts

Which lookup functions find a value equal or greater than the lookup value?
MATCH uses -1 to find larger value (lookup table must be sorted ZA). XLOOKUP uses 1 to find values greater and does not need to be sorted.

jasonb75

Well-known Member
Joined
Dec 30, 2008
Messages
10,687
Office Version
2019
Platform
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, Moderator
Joined
Mar 2, 2007
Messages
16,687
Office Version
2013
Platform
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,007
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
142

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,192
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
142
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>
 

Subscribe on YouTube

Watch MrExcel Video

Forum statistics

Threads
1,106,635
Messages
5,512,528
Members
408,902
Latest member
VicRattlehead

This Week's Hot Topics

  • Sort code advice please
    Hi, I have the code below which im trying to edit but getting a little stuck. This was the original code which worked fine,columns A-F would sort...
  • SUMPRODUCT with nested If statement
    Hi everyone, Hope you're all well. I'm hoping someone will be able to point me in the right direction with a problem I'm having with a SUMPRODUCT...
  • VBA - simple sort is killing me!
    Hello all! This should be so easy, but not for me, apparently! I have a table of data that can be of varying lengths and widths. My current macro...
  • Compare Two Lists
    I have two Lists and I need to be able to Identify differences between them. List 100 comes from a workbook - the other is downloaded form the...
  • Formula that deducts points for each code I input.
    I am trying to create a formula that will have each student in my class start at 100 points and then for each code that I enter (PP for Poor...
  • Conditional formatting formula required for day of week and a value
    Hi, I have a really simple spreadsheet where column A is the date, column B is the activity total shown as a number and column C states the day of...
Top