Results 1 to 5 of 5
  1. #1
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ISNA (xl 2k but probably all)

    I'm trying to replace the #N/A error message generated from a Vlookup when the target doesn't exist by the value zero.
    My formula, of the type
    =IF(isna(VLOOKUP(A4,Oct03!$A:$B,2,FALSE),0,VLOOKUP (A4,Oct03!$A:$B,2,FALSE))
    fails. (the FALSE condition is there to force only an exact match, if there is one.)
    It looks amazingly like one quoted by HansV in post 402652 -
    =IF(ISNA(VLOOKUP(...),"",VLOOKUP(...))
    What am I doing wrong?
    Thanks.

  2. #2
    WS Lounge VIP sdckapr's Avatar
    Join Date
    Jul 2002
    Location
    Pittsburgh, Pennsylvania, USA
    Posts
    11,225
    Thanks
    14
    Thanked 342 Times in 335 Posts

    Re: ISNA (xl 2k but probably all)

    You forgot a close parentheses for the isna:
    =IF(ISNA(VLOOKUP(A4,Oct03!$A:$B,2,FALSE)<font color=red>)</font color=red>,0,VLOOKUP(A4,Oct03!$A:$B,2,FALSE))

    Steve

  3. #3
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISNA (xl 2k but probably all)

    What do you mean by the formula fails? Do you get an error message? If so, what error? Do you get results that you do not expect? If so, what?

    The formula in your message does not have balanced parenthesis. There is one more left parenthesis that right parenthesis. Therefore, this formula could not have been entered into a cell. That makes it a little difficult for us to guess what is going on. The formula below, with the parenthesis corrected, seems to do what you are asking:

    <pre>=IF(ISNA(VLOOKUP(A4,Oct03!$A:$B,2,FALSE)),0,V LOOKUP(A4,Oct03!$A:$B,2,FALSE))
    </pre>

    Legare Coleman

  4. #4
    3 Star Lounger
    Join Date
    Jan 2004
    Location
    Birmingham, West Midlands, United Kingdom
    Posts
    219
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISNA (xl 2k but probably all)

    Thanks very much, both!
    Your identical solutions to my carelessness are appreciated.
    Steve's works, I haven't tested Legare's **grin**

  5. #5
    Uranium Lounger
    Join Date
    Jan 2001
    Location
    South Carolina, USA
    Posts
    7,295
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISNA (xl 2k but probably all)

    I tested it. <img src=/S/cheers.gif border=0 alt=cheers width=30 height=16>
    Legare Coleman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •