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

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

    The last 0 is a parameter in the ROUND() function telling Excel how many decimals to round to. You can't change that to a "". Are you sure that there is a number in AJ2 and the VLOOKUP is resulting in a number?


    Maybe try the VLOOKUP on it's own to see what it yields.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

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

    Ali :)


    Enthusiastic self-taught user of MS Excel who's always learning!
    If anyone has helped you towards solving a problem, then you can thank them by clicking on the like icon bottom right.

    :!:Forum Rules

    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.

    Sorry, I meant is AI2 a number? Check also column 19 of the ParameterTable to see if it is numeric and if there are any VALUE errors in there.


    Somehow this part $AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE) is not resulting as a number that the ROUND function can use.


    Can you post a sample workbook showing the problem?

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    Thanks SO much for your patience!


    I got confused there. That's exactly right! It's when it uses the formula. It adds a "0" when there's no data in this cell $AI2, in this case.


    If $AS$2="No" is TRUE it works perfect. No "0" just a blank.


    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.


    _ParameterTable,19 is always a number. It's a "1" when it doesn't add a weight %, and when I need the customer tables to be increased it's whatever factor I need.


    Does that make more sense?



    Quote from NBVC

    Sorry, I meant is AI2 a number? Check also column 19 of the ParameterTable to see if it is numeric and if there are any VALUE errors in there.


    Somehow this part $AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE) is not resulting as a number that the ROUND function can use.


    Can you post a sample workbook showing the problem?

    Without see the workbook, it's difficult to fully understand the problem...


    in the meantime, does this help?
    =IF($AS$2="No","",IFERROR(ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable,19,FALSE),0),""))

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    You don't show the "Config" sheet that your formula is referencing!


    If AS2 is resulting in #N/A! then there is no match found. The IFERROR() part of my formula should return a blank in that case.

    Where there is a will there are many ways. Finding one that works for you is the challenge!

    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


    Quote from NBVC

    You don't show the "Config" sheet that your formula is referencing!


    If AS2 is resulting in #N/A! then there is no match found. The IFERROR() part of my formula should return a blank in that case.

    Thank you, thank you, thank you!!!


    PERFECT!


    I was just going to ask what the [] was after the _ParameterTable and I noticed my version of Excel (2010) removed them. It does EXACTLY what I needed!...No "0" when the corresponding $AI cell is blank!


    Quote from arthurbr

    Perhaps =IF(OR($AS$2="No",$AI2=""),"",ROUND($AI2*VLOOKUP($AJ$2,_ParameterTable[],19,0),0))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!

Register Yourself Login