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

Which Excel functions can ignore hidden rows?
The SUBTOTAL and AGGREGATE functions ignore hidden rows. AGGREGATE can also exclude error cells and more.
=if (iserror(your_function_here),0,your_function_here)
 
Upvote 0
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)
 
Upvote 0
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!!
 
Upvote 0
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
 
Upvote 0
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:
Upvote 0
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 :)
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,213,487
Messages
6,113,941
Members
448,534
Latest member
benefuexx

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