Results 1 to 7 of 7
  1. #1
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Not displaying #N/A within an Array formula (Excel 97)

    I am using the following formula to look up the combination of two cells to look up the two matching cells on another sheet, and then return the balance in another cell:

    {=INDEX(Sheet1!$F$11:$F$14,MATCH(Sheet2!C11&Sheet2 !D11,(Sheet1!$C$11:$C$19&Sheet1!$D$11:$D$19),0),1) }

    It's returning #N/A when it can't find a match, which I would expect it to do. I want it to display a 0 instead of #N/A if it can't find a match. I've used the ISERROR nested function to do that before, but I cannot figure out how to incorporate it into this array formula. Any ides? Thanks! (file attached)

    -Kelley
    Attached Files Attached Files

  2. #2
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Wetherby, Yorkshire, England
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not displaying #N/A within an Array formula (Excel 97)

    Your answer was in a recent WOW mailer but I can't remember which one (2 or 3 issues back). You use "=IF(ISNA(Your Formula),0,(Your Formula))".

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

    Re: Not displaying #N/A within an Array formula (Excel 97)

    This works for me:

    <pre>=IF(ISERROR(INDEX(Sheet1!$F$11:$F$14,MATCH(Sh eet2!C11&Sheet2!D11,(Sheet1!$C$11:$C$19&Sheet1!$D$ 11:$D$19),0),1)),0,INDEX(Sheet1!$F$11:$F$14,MATCH( Sheet2!C11&Sheet2!D11,(Sheet1!$C$11:$C$19&Sheet1!$ D$11:$D$19),0),1))
    </pre>

    Legare Coleman

  4. #4
    Star Lounger
    Join Date
    Jan 2001
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Not displaying #N/A within an Array formula (Excel 97)

    Legare,

    That worked, I just needed to play with it a bit more, I think I was not including the right number of parenthesis, and that was throwing the formula off.

    Thanks for the help!

    -Kelley

  5. #5
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Not displaying #N/A within an Array formula (Excel 97)

    <img src=/S/sarcasm.gif border=0 alt=sarcasm width=15 height=15>Wide enough post Legare? <img src=/S/wink.gif border=0 alt=wink width=15 height=15>
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

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

    Re: Not displaying #N/A within an Array formula (Excel 97)

    For this answer. Unfortunately, since there is no continuation character for formula, I don't know of a non-confusing way to break a formula that can be copied and pasted into a cell.
    Legare Coleman

  7. #7
    Super Moderator WebGenii's Avatar
    Join Date
    Jan 2001
    Location
    Redcliff, Alberta, Canada
    Posts
    4,066
    Thanks
    2
    Thanked 5 Times in 5 Posts

    Re: Not displaying #N/A within an Array formula (Excel 97)

    It's okay - just teasing - much better than saying "Holy <img src=/w3timages/censored.gif alt=censored border=0>" - look how long that formula is!

    Ooops! now I said it.
    [b]Catharine Richardson (WebGenii)
    WebGenii Home Page
    Moderator: Spreadsheets, Other MS Apps, Presentation Apps, Visual Basic for Apps, Windows Mobile

Posting Permissions

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