Help with #N/A to 0.00

L

Legacy 154275

Guest
Hi all :)

I cant seem to get my Item and Retail Price etc cells to show 0.00 or at least 0. I know there has to be a way, but I have run out of ideas.

Any help would be great please :)

This is in the Item cell
=VLOOKUP(Input!A14,Wines!A5:B63,2)

This is in the Retail Price Cells
=LOOKUP(Input!A14,Wines!A5:A63,Wines!H5:H63)

<TABLE style="WIDTH: 448pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=596><COLGROUP><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 60pt; mso-width-source: userset; mso-width-alt: 2925" width=80><COL style="WIDTH: 58pt; mso-width-source: userset; mso-width-alt: 2816" width=77><COL style="WIDTH: 48pt; mso-width-source: userset; mso-width-alt: 2340" span=2 width=64><COL style="WIDTH: 47pt; mso-width-source: userset; mso-width-alt: 2267" width=62><COL style="WIDTH: 48pt" width=64><TBODY><TR style="HEIGHT: 29.25pt; mso-height-source: userset" height=39><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #d99795; WIDTH: 139pt; HEIGHT: 29.25pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=39 width=185>Item</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d99795; WIDTH: 60pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=80>Quanity</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d99795; WIDTH: 58pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=77>Retail Price</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d99795; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>Discount</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d99795; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=64>Net Price Exc GST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d99795; WIDTH: 47pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 width=62>Net Price Inc GST</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #d99795; WIDTH: 48pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 width=64>GST</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: white; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl67 height=20 align=middle>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl66 align=right>0</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>#N/A</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: white; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl68>#N/A</TD></TR></TBODY></TABLE>
 

Some videos you may like

Excel Facts

What does custom number format of ;;; mean?
Three semi-colons will hide the value in the cell. Although most people use white font instead.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
NOPE.
u put your Lookup formulas in mine where it says your_function_here, so when the Lookup or Vlookup returns an error the result will be 0
(if i understood correctly what you're after)
 
L

Legacy 154275

Guest

ADVERTISEMENT

bobsan42 Thank you I will go see, Its not an error as such, its the fact the the linked cells have no value in them because this will be a templete.

Hope that makes sense

BB in a bit if I fail LOL

Thanks again!!
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
OK - i understand now (i think)
then you more probably need "" instead of 0:
=if (iserror(your_VLOOKUP_function_here),"",your_function_here)
or
=if (isna(your_VLOOKUP_function_here),"",your_function_here)
give it a try
cheers
 
L

Legacy 154275

Guest

ADVERTISEMENT

NOPE.
u put your Lookup formulas in mine where it says your_function_here, so when the Lookup or Vlookup returns an error the result will be 0
(if i understood correctly what you're after)
I have failed miserably as expected LOL I under stabd your post and can see the sense in it, but I lack the syntax :(
I came up with various versions of this

=IF(iserror(=VLOOKUP(Input!A14,Wines!A5:B63,2),0,=VLOOKUP(Input!A14,Wines!A5:B63,2)
 
Last edited by a moderator:
L

Legacy 154275

Guest
OK - i understand now (i think)
then you more probably need "" instead of 0:
=if (iserror(your_VLOOKUP_function_here),"",your_function_here)
or
=if (isna(your_VLOOKUP_function_here),"",your_function_here)
give it a try
cheers
Will do tx :)
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,343
I have failed miserably as expected LOL I under stabd your post and can see the sense in it, but I lack the syntax :(
I came up with various versions of this

=IF(iserror(=VLOOKUP(Input!A14,Wines!A5:B63,2),0,=VLOOKUP(Input!A14,Wines!A5:B63,2)
remove the extra "=" and put some ")"

=IF(iserror(VLOOKUP(Input!A14,Wines!A5:B63,2)),"",VLOOKUP(Input!A14,Wines!A5:B63,2))
 
L

Legacy 154275

Guest
remove the extra "=" and put some ")"

=IF(iserror(VLOOKUP(Input!A14,Wines!A5:B63,2)),"",VLOOKUP(Input!A14,Wines!A5:B63,2))

Awwwwwwwwwwwwwwwww Right sorry very new to this, Thank you, it is becoming clearer LOL

Cheers mate off to MAKE it worl :D
 

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