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>
 

Excel Facts

Who is Mr Spreadsheet?
Author John Walkenbach was Mr Spreadsheet until his retirement in June 2019.

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,871
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
=if (iserror(your_function_here),0,your_function_here)
 

bobsan42

Well-known Member
Joined
Jul 14, 2010
Messages
1,871
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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,871
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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,871
Office Version
  1. 365
  2. 2019
  3. 2016
  4. 2013
Platform
  1. Windows
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
 
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,168,109
Messages
5,857,441
Members
431,879
Latest member
KiwDaWabbit

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