IF function returning 0(blank) instead of TRUE.

  • I've googled this and i'm stumped. In short, I have a worksheet to display information from another worksheet using a drop menu. The following function is returning a 0 (or blank after tools->view zero cell blank checked), instead of the TRUE argument.

    =IF(ISNA(VLOOKUP(B302,ACCOUNTS!$A$2:$K$300,4,FALSE)),"Not Found",(VLOOKUP(B302,ACCOUNTS!A2:K300,4,FALSE)))

    The cell in question is blank, nothing in there. However, if I type something then that will appear.

    Why can't I get it to say "N/A" instead of returning a blank, but it WILL show if something is there?

    Thanks for any and all help! Let me know if clarification is needed.

  • Re: IF function returning 0(blank) instead of TRUE.


    Hello sixner,


    Welcome to Ozgrid.
    Please do not use code tags on formula. These tags are reserved only for use with VBA code.


    VLOOKUP automatically returns #N/A if a match is not found. Why nest this in an IF function and force Excel to do the look up twice?


    Adapt on the methods from the following page.


    Stop The #N/A! Error in VLOOKUP

  • Re: IF function returning 0(blank) instead of TRUE.


    Sorry about using the code tag's.

    As for my issue, i'm not getting an #n/a error, or any error at all. It's just a blank cell where it should be saying "Not Found". I'm using vlookup because i'm using a drop down of 500+ customers, and have various fields I need to pull from. This is an easy to use sheet that provides all the info for one customer, rather than scrolling through hundreds of fields and skimming for the column you need.

    Of note, the inital formula does NOT produce a "not found" when finding a blank cell.. but this one DOES.
    =IF(ISNA(VLOOKUP(D303,DUNS!$A$3:$J$504,10,FALSE)),"Not Found",(VLOOKUP(D303,DUNS!$A$3:$J$504,10,FALSE)))

    I can't find the issue here.

  • Re: IF function returning 0(blank) instead of TRUE.


    It would be best to upload a sample workbook that shows the problem. Remove any sensitive data before uploading.
    There is no obvious reason that I can see for the first formula to not work.


    However, in the first formula you do have an extra set of parentheses that are not needed with the VLOOKUP in the last argument. (Same with the second formula on this point).
    Also, your range in the second instance of VLOOKUP is relative, whereas the first VLOOKUP uses absolute references


    Not sure if it matters, but in comparing the formulas your ranges have different starting rows and do not encompass the same number of rows. (However, they reference different sheets, so this is likely not an issue)


    Can't offer any more help without seeing the workbook.

  • Re: IF function returning 0(blank) instead of TRUE.


    Sounds like you are misunderstanding how VLOOKUP works - if you use a formula like this


    =VLOOKUP("x",A2:B10,2,0)


    Then if "x" is found in A2 the formula returns the value of B2 - if B2 is a blank you don't get #N/A, that only occurs if "x" isn't found anywhere in the lookup range (A2:A10). If you want to return some message if B2 is blank then you can do that like this


    =IF(Vlookup_formula="","blank",Vlookup_formula)


    You can replace "blank" with any text you want, even "" (a blank)


    If you also want "not found" then you could add an IFERROR function to that, i.e.


    =IFERROR(IF(Vlookup_formula="","blank",Vlookup_formula),"not found")