Results 1 to 5 of 5

To return blank instead of #N/A

This is a discussion on To return blank instead of #N/A within the Excel Questions forums, part of the Question Forums category; Would anyone be able to help me modify this. I am trying to make it so if the result is ...

  1. #1
    Board Regular
    Join Date
    Jun 2010
    Location
    Seattle Wa.
    Posts
    600

    Default To return blank instead of #N/A

    Would anyone be able to help me modify this. I am trying to make it so if the result is #N/A, it will return "" or blank.

    =INDEX('Schedule Tool'!$C$1:$BT$1,MATCH(1,OFFSET('Schedule Tool'!$D$2:$BU$2,MATCH(A2,'Schedule Tool'!$A$3:$A$30,0),,1),0))

    Thanks everyone in advance for any help

    sd

  2. #2
    Board Regular
    Join Date
    Mar 2011
    Location
    USA USA USA USA
    Posts
    556

    Default Re: To return blank instead of #N/A

    =IF(ISNA(INDEX('Schedule Tool'!$C$1:$BT$1,MATCH(1,OFFSET('Schedule Tool'!$D$2:$BU$2,MATCH(A2,'Schedule Tool'!$A$3:$A$30,0),,1),0))),"",INDEX('Schedule Tool'!$C$1:$BT$1,MATCH(1,OFFSET('Schedule Tool'!$D$2:$BU$2,MATCH(A2,'Schedule Tool'!$A$3:$A$30,0),,1),0)))


    try this

    This is a simple IF statement, your original formula is in pink, the new part is in blue.

    Statement now reads: If "original formula" returns #N/A, change to ""; else, perform "original formula".
    Last edited by Desu Nota from Columbus; Jul 12th, 2011 at 02:40 PM.
    "Frankly my dear, I don't give a ****." --Rhett Butler

    If posting code, use
    code tags...

    All posts based on Excel 2003

  3. #3
    Board Regular
    Join Date
    Jun 2010
    Location
    Seattle Wa.
    Posts
    600

    Default Re: To return blank instead of #N/A

    Quote Originally Posted by Desu Nota from Columbus View Post
    =IF(ISNA(INDEX('Schedule Tool'!$C$1:$BT$1,MATCH(1,OFFSET('Schedule Tool'!$D$2:$BU$2,MATCH(A2,'Schedule Tool'!$A$3:$A$30,0),,1),0))),"",INDEX('Schedule Tool'!$C$1:$BT$1,MATCH(1,OFFSET('Schedule Tool'!$D$2:$BU$2,MATCH(A2,'Schedule Tool'!$A$3:$A$30,0),,1),0)))


    try this

    This is a simple IF statement, your original formula is in pink, the new part is in blue.

    Statement now reads: If "original formula" returns #N/A, change to ""; else, perform "original formula".

    Thank you soo much, worked perfect!!

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    65,124

    Default Re: To return blank instead of #N/A

    Quote Originally Posted by sdoppke View Post
    Would anyone be able to help me modify this. I am trying to make it so if the result is #N/A, it will return "" or blank.

    =INDEX('Schedule Tool'!$C$1:$BT$1,MATCH(1,OFFSET('Schedule Tool'!$D$2:$BU$2,MATCH(A2,'Schedule Tool'!$A$3:$A$30,0),,1),0))

    Thanks everyone in advance for any help

    sd
    Also, if the outcome is expected to be text-valued and you are on an Excel version prior 2007...
    Code:
    =LOOKUP(REPT("z",255),CHOOSE({1,2},"",
       T(INDEX('Schedule Tool'!$C$1:$BT$1,
           MATCH(1,OFFSET('Schedule Tool'!$D$2:$BU$2,
           MATCH(A2,'Schedule Tool'!$A$3:$A$30,0),,1),0)))))
    Assuming too much and qualifying too much are two faces of the same problem.

  5. #5
    Board Regular T. Valko's Avatar
    Join Date
    May 2009
    Location
    Pittsburgh
    Posts
    16,133

    Default Re: To return blank instead of #N/A

    Quote Originally Posted by sdoppke View Post
    Would anyone be able to help me modify this. I am trying to make it so if the result is #N/A, it will return "" or blank.

    =INDEX('Schedule Tool'!$C$1:$BT$1,MATCH(1,OFFSET('Schedule Tool'!$D$2:$BU$2,MATCH(A2,'Schedule Tool'!$A$3:$A$30,0),,1),0))

    Thanks everyone in advance for any help

    sd
    If you're using Excel 2007 or later...

    =IFERROR(INDEX('Schedule Tool'!$C$1:$BT$1,MATCH(1,OFFSET('Schedule Tool'!$D$2:$BU$2,MATCH(A2,'Schedule Tool'!$A$3:$A$30,0),,1),0)),"")
    .
    Biff
    Microsoft MVP - Excel

    Using Excel 2002, 2007
    KISS - Keep It Simple Stupid

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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


DMCA.com