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

[wittonlin]

...if I change the last zero to "" for example.

=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.

 

[admiral100]

To fix columns V and W Change AS2 to
=IFERROR(VLOOKUP($AJ$2,'C:\Users\Admin\Downloads\[LeadSelectionMaster_ReadyNow.xlsm]Config'!$A$11:$T$26,10,FALSE),"")

Then change X2 to
=IFERROR(IF($W2="","",ROUND($AI2*VLOOKUP($AJ$2,'C:\Users\Admin\Downloads\[LeadSelectionMaster_ReadyNow.xlsm]Config'!$A$11:$T$26,19,FALSE),0)),"")

 

[wittonlin]

I'm so sorry I wasn't more careful with the uploaded file!

It was missing a sheet, therefore also missing _ParameterTable (named range)

Cell AS2 isn't an issue. It will ALWAYS be either "Yes" or "No".

My issue, as you can now see because I added the new formulas into the yellow highlight column on the G2-8 sheet, is that I want to use Cell AS2 as the KEY to decide whether to display values rather than using the W column same row (i.e. W2, W3, etc.) used in the old formulas.

As you can see though, when the $AI column value is blank, instead of displaying nothing (blank) in column X, it displays "0".

http://wikisend.com/download/193034/Book134.xlsm

To fix columns V and W Change AS2 to
=IFERROR(VLOOKUP($AJ$2,'C:\Users\Admin\Downloads\[LeadSelectionMaster_ReadyNow.xlsm]Config'!$A$11:$T$26,10,FALSE),"")

Then change X2 to
=IFERROR(IF($W2="","",ROUND($AI2*VLOOKUP($AJ$2,'C:\Users\Admin\Downloads\[LeadSelectionMaster_ReadyNow.xlsm]Config'!$A$11:$T$26,19,FALSE),0)),"")

 

[admiral100]

Sorry but AS2 is the issue

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

As you can see , ONLY when AS2 = "No" then column X = "" (blank)

Regarding the second part of the IF , you can try to add another if

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

 

[wittonlin]

Someone came up with the answer! It's an IF(OR) function that worked.

For the record this string works perfect and when the corresponding $AI cell is blank so is the cell that the formula's in!

=IF(OR($AS$2="No",$AI8=""),"",ROUND($AI8*VLOOKUP($AJ$2,_ParameterTable,19,0),0))

Thank you all for your efforts!

Mark

 

[Jonmo1]

You're welcome.

Someone? hmmm....

 

[wittonlin]

Well I didn't want to make a deal of it...lol. The other forum.

You're welcome.

Someone? hmmm....