Results 1 to 10 of 10

remove #N/A in excel

This is a discussion on remove #N/A in excel within the Excel Questions forums, part of the Question Forums category; Hi, i am using these formula below; =IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)), VLOOKUP(B2,Sheet2!A:F,2,FALSE),VLOOKUP(A2,Sheet2!A:F,2,FALSE)) sheet 1 Item Alternative Items Jun-09 Jul-09 Aug-09 Sep-09 Oct-09 12345 ...

  1. #1
    New Member
    Join Date
    Mar 2010
    Location
    Philippines
    Posts
    37

    Default remove #N/A in excel

    Hi,

    i am using these formula below;

    =IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),
    VLOOKUP(B2,Sheet2!A:F,2,FALSE),VLOOKUP(A2,Sheet2!A:F,2,FALSE))

    sheet 1
    Item Alternative Items Jun-09 Jul-09 Aug-09 Sep-09 Oct-09
    12345 #N/A #N/A #N/A #N/A #N/A
    12398 #N/A #N/A #N/A #N/A #N/A
    12455 45678 0 0 0 0 0
    004A166 #N/A #N/A #N/A #N/A #N/A
    004A167 #N/A #N/A #N/A #N/A #N/A
    00510R0810 510R081 0 0 0 0 0
    00675R0140 675R014 0 0 0 0 0

    sheet 2
    Item Jun-09 Jul-09 Aug-09 Sep-09 Oct-09
    12345 #N/A #N/A #N/A #N/A #N/A
    12378 #N/A #N/A #N/A #N/A #N/A
    45678 0 0 0 0 0
    004A166 #N/A #N/A #N/A #N/A #N/A
    004A167 #N/A #N/A #N/A #N/A #N/A
    510R081 0 0 0 0 0
    675R014 0 0 0 0 0


    how can i remove the #N/A without using Replace?

    Thanks

  2. #2
    Board Regular
    Join Date
    Apr 2004
    Posts
    6,715

    Default Re: remove #N/A in excel

    As the #N/A is the result of a formula, Replace wouldnt work anyway.

    As the "true" part of your Iserror() function, return 0 if the formula evaluates to an error.
    Yesterday I felt on top of the world. Today its falling in on me.

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

    Default Re: remove #N/A in excel

    If the formula is expected to return a number, try on Excel versions prior to 2007...

    Code:
    =LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,
        VLOOKUP(B2,Sheet2!A:F,2,0),VLOOKUP(A2,Sheet2!A:F,2,0)))
    If so desired, custom format the formula cell as:

    [=0]"";General
    Assuming too much and qualifying too much are two faces of the same problem.

  4. #4
    New Member
    Join Date
    Mar 2010
    Location
    Philippines
    Posts
    37

    Default Re: remove #N/A in excel

    Hi Aladin,

    i tried the formula you gave.
    lookup(9.99999999999999E+307,CHOOSE((1,2,3,),0,VLOOKUP(B2,Sheet2!A:F,2,0),VLOOKUP(Sheet1!A2,Sheet2!A:F,2,0)))

    there is an error that say

    "The formula that you typed contains an error."

    Is there another formula that you can recommend?

    thanks,

  5. #5
    Board Regular
    Join Date
    Oct 2008
    Location
    Melbourne, Australia
    Posts
    123

    Default Re: remove #N/A in excel

    Your original formula basically says that if it can't find A2 in your lookup table, then try looking up B2.

    So the only time you would be getting #N/As is if it cannot find B2 in your lookup table either.

    Someone will probably add something less cumbersome but you should be able to add another error trap inside your error trap.

    =IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),
    IF(ISERROR(VLOOKUP(B2,Sheet2!A:F,2,FALSE)),"ERROR",VLOOKUP(B2,Sheet2!A:F,2,FALSE)),VLOOKUP(A2,Sheet2!A:F,2,FALSE))

    Should return the text string of "Error" when neither A2 or B2 appear in your lookup tables. Much easier to replace. Or you can make it return blank ("") or whatever you like.
    ---------------------------
    Yes I know there are better ways to do it.
    My brain is a Double. Add in too many values and it Overflows.

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

    Default Re: remove #N/A in excel

    Quote Originally Posted by aivijardin_09 View Post
    Hi Aladin,

    i tried the formula you gave.
    lookup(9.99999999999999E+307,CHOOSE((1,2,3,),0,VLOOKUP(B2,Sheet2!A:F,2,0),VLOOKUP(Sheet1!A2,Sheet2!A:F,2,0)))

    there is an error that say

    "The formula that you typed contains an error."

    Is there another formula that you can recommend?

    thanks,
    It's {1,2,3}, not (1,2,3,) as you have it... Thus:

    =LOOKUP(9.99999999999999E+307,CHOOSE({1,2,3},0,VLOOKUP(B2,Sheet2!A:F,2,0),VLOOKUP(Sheet1!A2,Sheet2!A:F,2,0)))
    Assuming too much and qualifying too much are two faces of the same problem.

  7. #7
    New Member
    Join Date
    Mar 2010
    Location
    Philippines
    Posts
    37

    Default Re: remove #N/A in excel

    hi Beezkneez,

    i tried your formula there is an error that says "you've entered to many arguments for this function".

    what should i do?

    Sorry i'm not that good in excel.

    Thanks.

  8. #8
    Board Regular
    Join Date
    Feb 2010
    Location
    Mumbai
    Posts
    321

    Default Re: remove #N/A in excel

    try this in the original sheet

    If(iserror(your formula),"",your formula)

  9. #9
    New Member
    Join Date
    Mar 2010
    Location
    Philippines
    Posts
    37

    Default Re: remove #N/A in excel

    hi yogeshmaney,

    i used the formula below;

    =IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),"",VLOOKUP(B2,Sheet2!A:F,2,FALSE))

    and the result is this (i used the formula in Jun-09 only);

    Item Alternative Items Jun-09 Jul-09 Aug-09 Sep-09 Oct-09
    12345 #N/A #N/A #N/A #N/A
    12398 #N/A #N/A #N/A #N/A
    12455 45678 0 0 0 0
    004A166 #N/A #N/A #N/A #N/A
    004A167 #N/A #N/A #N/A #N/A
    00510R0810 510R081 0 0 0 0
    00675R0140 675R014 0 0 0 0

    but when i added a value on sheet 2 (see below table), the item 12455 should have a value of 12. there should be a value in Jun-09 which is 12.

    Item Jun-09 Jul-09 Aug-09 Sep-09 Oct-09
    12345
    12378
    45678 12 0 0 0 0
    004A166
    004A167
    510R081 0 0 0 0 0
    675R014 0 0 0 0 0

    thanks,
    aivi

  10. #10
    Board Regular
    Join Date
    Mar 2010
    Location
    seattle
    Posts
    394

    Default Re: remove #N/A in excel

    your formula:
    =IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),"",VLOOKUP(B2,Sheet2!A:F,2,FALSE))

    says if there's an error looking for A2, then try B2. So you need to capture an error looking for B2, as well:


    =IF(ISERROR(VLOOKUP(A2,Sheet2!A:F,2,FALSE)),"",IF(ISERROR(VLOOKUP(B2,Sheet2!A:F,2,FALSE)),"",VLOOKUP(B2,Sheet2!A:F,2,FALSE)))
    I want Excel and Access to get married and have a baby. And I would call that baby 'Accel'

    Windows 7, Office 2010

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