Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi gang,
    I have a VLOOKUP that needs to match the exact value (FALSE).
    Works just fine except when it can't find a match. I get the #N/A.
    I want to cell to remain empty is no match is found.
    Please help.
    Tks.
    Johanne Champagne
    Montreal (Quebec) CANADA

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Let's say you have a formula

    =VLOOKUP(A1,D1:F100,3,FALSE)

    If you want to suppress #N/A if there is no match, change it to

    =IF(ISNA(VLOOKUP(A1,D1:F100,3,FALSE)),"",VLOOKUP(A 1,D1:F100,3,FALSE))

  3. #3
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    BTW, in Excel 2007 and later, you can change a formula such as

    =VLOOKUP(A1,D1:F100,3,FALSE)

    to

    =IFERROR(VLOOKUP(A1,D1:F100,3,FALSE),"")

    The IFERROR function is not available in Excel 2003 and earlier.

  4. #4
    3 Star Lounger
    Join Date
    Nov 2002
    Location
    New York, New York, USA
    Posts
    266
    Thanks
    0
    Thanked 19 Times in 19 Posts
    Don't know if this matters but if the formula is going to copied hundreds or thousands of times in your workbook it can slow down Excel.

    If so consider having 2 Functions. The first would be the Lookup function is one cell. The second IF statement would go in the Active Cell where you want the final results.

    This can speed up the Workbook.


    Regards,

    Tom Duthie

  5. #5
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Hans. It worked just fine. One question: will this workbook still work when we switch to 2007 in the coming year or will I have to change the formula to the IFERROR one?
    Johanne Champagne
    Montreal (Quebec) CANADA

  6. #6
    2 Star Lounger
    Join Date
    Jan 2007
    Location
    Gray, Louisiana, USA
    Posts
    289
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello Johanne,

    I use Excel 2007 and I haven't had to change any formula's such as this.

  7. #7
    2 Star Lounger
    Join Date
    Mar 2007
    Location
    Montreal, Quebec
    Posts
    125
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Perfect. Thanks all.
    jc
    Johanne Champagne
    Montreal (Quebec) CANADA

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Quote Originally Posted by JC@OR View Post
    Thanks Hans. It worked just fine. One question: will this workbook still work when we switch to 2007 in the coming year or will I have to change the formula to the IFERROR one?
    As Stan wrote, you won't have to change the formulas. I just mentioned IFERROR because it's a new feature in Excel 2007 - it makes handling errors a bit easier but you're not required to use it.

Posting Permissions

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