Results 1 to 7 of 7
  1. #1
    3 Star Lounger
    Join Date
    Aug 2004
    Posts
    361
    Thanks
    0
    Thanked 0 Times in 0 Posts

    ISNA formula (Excel 2002)

    =if(isna(VLOOKUP(B3,Employers!$A$1:$E$600,2,0),"", vlookup(b3,Employers!$a$1:$e$600)

    This formula should stop the #NA appearing in the cells, I believe. I have used the =if(ISNA...... with indexes and matches without problem. However when I enter this particular formula I get an error message which highlights the "", indicating that I have entered them incorrectly. I tried a space between the "" and get the same error message. What am I doing wrong, or will the =if(ISNA..... not work in conjunction with a Vlookup? Thanks for any help.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ISNA formula (Excel 2002)

    There is a closing bracket missing after both VLOOKUP parts, and you omitted the 3rd and 4th argument to the second VLOOKUP part. Here is the corrected formula:

    =IF(ISNA(VLOOKUP(B3,Employers!$A$1:$E$600,2,0)),"" ,VLOOKUP(B3,Employers!$A$1:$E$600,2,0))

  3. #3
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISNA formula (Excel 2002)

    Hans,

    Is it possible to do the same thing without using VLOOKUPs? I am having problems adding to this formula:

    =LOOKUP(IDate,Tran_Date,Jumbo_ND)

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ISNA formula (Excel 2002)

    The idea should be the same:

    =IF(ISNA(LOOKUP(IDate,Tran_Date,Jumbo_ND)),"",LOOK UP(IDate,Tran_Date,Jumbo_ND))

  5. #5
    2 Star Lounger
    Join Date
    Mar 2004
    Location
    Mechanicsville, Virginia, USA
    Posts
    130
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISNA formula (Excel 2002)

    Mucho thanks!!!

  6. #6
    3 Star Lounger
    Join Date
    Jan 2002
    Location
    The Hague, Netherlands
    Posts
    283
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: ISNA formula (Excel 2002)

    Hans,

    There is no need for ISNA. If invoking a LOOKUP formula is justified, that is, Tran_Date in this paricular case in ascending order, there will be just one situation in which #N/A will obtain. That is when IDate < INDEX(Tran_Date,1). Since LOOKUP is very fast, it's a pity to slow down its speed with a scheme that requires "computing twice".

    If LOOKUP is indeed justified, the following options would be better:

    1)

    =IF(IDate<INDEX(Tran_Date,1),"",LOOKUP(IDate,Tran_ Date,Jumbo_ND))

    2} Add the following at the beginning of Tran_Date:

    -9.99999999999999E+307

    and at the beginning of Jumbo_ND

    =""

    and just invoke:

    =LOOKUP(IDate,Tran_Date,Jumbo_ND)

    unmodified.

    The last option is the best.
    Microsoft MVP - Excel

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: ISNA formula (Excel 2002)

    Thanks, Aladin, that makes sense.

Posting Permissions

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