Trouble w VLOOKUP (later in formula) displaying "0"s or #VALUE!

W

wittonlin

Board Regular
Joined
Jan 30, 2016
Messages
144
...if I change the last zero to "" for example.

Code: 
=IF($AS$2="No","",ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE),0))

Works perfect if the initial IF is false; it displays a BLANK. But I can't edit the last zero so that an error or problem in the formula displays a BLANK as well.

Let me know if you can help.

Thanks!


Also posted here.
 

Excel Facts

Can you sort left to right?
To sort left-to-right, use the Sort dialog box. Click Options. Choose "Sort left to right"
Sort by date Sort by votes
Thank you for trying!

This option adds a "0" when $AS$2="No" unfortunately. I can't use the formatting options either, because sometimes there's a legitimate zero in the cell I'm adding the formula. I just need to figure out how to make this work {$AS$2="No","",} and actually not add a zero. :)

I don't THINK it matters but jic, $AJ$2 is not a number. It's a value similar to this...G2-1.


Aladin Akyurek said:
Try...

=IF($AS$2="No","",IFERROR(ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE),0),""))​
Click to expand...
 
Upvote 0
Hi,

Did you try to add space ? What is the result if you change from "" to "Good" ? =IF($AS$2="No","Good".....

=IF($AS$2="No"," ",ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE),0))
 
Upvote 0
Are you sure the formula is doing what you think it's doing?
Did you actually try the formula Aladin suggested?


These 2 statements seem to be backwards...
wittonlin said:
Code: 
=IF($AS$2="No","",ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE),0))

Works perfect if the initial IF is false; it displays a BLANK.
Click to expand...
No, if the initial IF is FALSE, it performs the Round function

wittonlin said:
This option adds a "0" when $AS$2="No"
Click to expand...
Again No, If $AS$2 = "No", then the formula returns ""


Maybe we should start from scratch. Forget the formula you currently have that doesn't do what you want.
Just describe in words what you want a formula to do.
 
Upvote 0
Thanks SO much for your patience! :)

I got confused there. You're exactly right. If $AS$2="No" is TRUE it works perfect. No "0" just a blank.

It's when it uses the formula, then it adds a "0" when there's no data in this cell $AI2, in this case.

I need to focus on the VLOOKUP portion: ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE)

And when there's NO value in $AI2, have the result of the formula a BLANK.

Does that make more sense?


Jonmo1 said:
Are you sure the formula is doing what you think it's doing?
Did you actually try the formula Aladin suggested?


These 2 statements seem to be backwards...

No, if the initial IF is FALSE, it performs the Round function


Again No, If $AS$2 = "No", then the formula returns ""


Maybe we should start from scratch. Forget the formula you currently have that doesn't do what you want.
Just describe in words what you want a formula to do.
Click to expand...
 
Upvote 0
Try changing the beginning IF to include an OR

=IF(OR($AS$2="No",$AI2="")
 
Last edited:
Upvote 0
HI wittonlin

Try this Formula for your reference i have shared with column headings

Output is in f Column




[TABLE="width: 1494"]
<colgroup><col><col span="2"><col><col span="5"></colgroup><tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]NO[/TD]
[TD]=IFERROR(IF(D2="NO","",ROUND(B2*VLOOKUP(C2,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]NO[/TD]
[TD]=IFERROR(IF(D3="NO","",ROUND(B3*VLOOKUP(C3,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]NO[/TD]
[TD]=IFERROR(IF(D4="NO","",ROUND(B4*VLOOKUP(C4,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]NO[/TD]
[TD]=IFERROR(IF(D5="NO","",ROUND(B5*VLOOKUP(C5,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]=IFERROR(IF(D6="NO","",ROUND(B6*VLOOKUP(C6,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]Yes[/TD]
[TD]=IFERROR(IF(D7="NO","",ROUND(B7*VLOOKUP(C7,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD]-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
 
Upvote 0
Ironically both these formulas work, after attempting to convert to your formula format. Problem is that both still add the '0' when referring cell ($AI10) is blank.

Code: 
=IFERROR(IF($AS$2="No","",ROUND($AI10*VLOOKUP($AJ$2,_ParameterTable,19,FALSE),0)),"-")
Code: 
=IFERROR(IF($AS$2="No","",ROUND($AI10*VLOOKUP($AJ$2,_ParameterTable,19,0),0)),"-")

Here's one of the sheets we pulled out. The column in question is in yellow highlight. http://wikisend.com/download/981160/Book133.xls

Notice the formula I use now doesn't add a zero when referring cell is blank but I'm trying to check a different cell ($AS$2) to kick in the calculation. :)


ravi2628 said:
HI wittonlin

Try this Formula for your reference i have shared with column headings

Output is in f Column




[TABLE="width: 1494"]
<tbody>[TR]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]1[/TD]
[TD]NO[/TD]
[TD]=IFERROR(IF(D2="NO","",ROUND(B2*VLOOKUP(C2,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD][/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]2[/TD]
[TD]NO[/TD]
[TD]=IFERROR(IF(D3="NO","",ROUND(B3*VLOOKUP(C3,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD][/TD]
[TD]5[/TD]
[TD]1[/TD]
[TD]1[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]3[/TD]
[TD]NO[/TD]
[TD]=IFERROR(IF(D4="NO","",ROUND(B4*VLOOKUP(C4,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]4[/TD]
[TD]NO[/TD]
[TD]=IFERROR(IF(D5="NO","",ROUND(B5*VLOOKUP(C5,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]=IFERROR(IF(D6="NO","",ROUND(B6*VLOOKUP(C6,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD]5[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD]6[/TD]
[TD]Yes[/TD]
[TD]=IFERROR(IF(D7="NO","",ROUND(B7*VLOOKUP(C7,$G$2:$J$12,3,0),0)),"-")[/TD]
[TD]-[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]
Click to expand...
 
Upvote 0
You must log in or register to reply here.

Similar threads

S
  • Question Question
Vlookuping matching values with hidden rounding errors
Replies
5
Views
536
awoohaw
awoohaw
C
  • Solved
Formula using match, indirect, and vlookup returning 0
Replies
1
Views
349
Ccruz444
C
D
  • Solved
Cannot COUNT/SUM Displayed Table Values, Table Filtered Values Out of Order
Replies
3
Views
388
Alex Blakenburg
A
N
  • Question Question
Highest Value(s) displaying text
Replies
8
Views
535
Fluff
Fluff
M
  • Question Question
VLOOKUP?
Replies
1
Views
222
MeaclH
M

Forum statistics

Threads
1,224,823
Messages
6,181,181
Members
453,022
Latest member
Mohamed Magdi Tawfiq Emam

Share this page

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