Results 1 to 6 of 6

VLOOKUP - Hide #N/A ERROR

This is a discussion on VLOOKUP - Hide #N/A ERROR within the Excel Questions forums, part of the Question Forums category; I'm using vlookup to search a block of data with a max of 31 rows, and a min of 0 ...

  1. #1
    New Member
    Join Date
    Aug 2007
    Posts
    10

    Default VLOOKUP - Hide #N/A ERROR

    I'm using vlookup to search a block of data with a max of 31 rows, and a min of 0 rows.

    This is data from a calendar in spreadsheet form. Only dates with work (event column) occur. So if there is work every day, then there would be 31 lines, or 30 lines or less in appropriate months with <31 days.
    All my calculations regarding vlookup work (when there is data on that line), I wont bore you with them here.



    PROBLEM:
    On the calendar which uses vlookup to get the data, it keys on the first column. On those dates (like 11/7) which don't exist, or in a 30 or 28 day month, I get #N/A errors, which is logical. I just want to hide the #N/A display.
    --------------------------------------------------------------
    11/3
    bkb (result for vlookup(a1,b7:f37,4,false) if entry in b7 exists in cells b7:b37 (Row 3)

    #N/A (result if data not in b7:b37. I just want to hide this) (ROW 4)


    A B C D E F
    1 (ROW 7)
    2 11/1 11/1 11/1 soc cll (ROW 8)
    3 11/3 11/4 11/6 bkb aus
    4 11/9 11/11 11/11 vb dal
    5
    6

    28 NO DATA ON DAYS WHICH DON'T EXIST
    29 NO DATA IF NO EVENT SCHEDULED THAT DATE
    30 (ROW 36)
    31 (ROW 37 )


    I'VE TRIED:

    =iferror(VLOOKUP(a1,b7:f37,4,FALSE),"") -- gives me the response #NAME?
    (is IFERROR really a function or is that an error in the "help" database???)

    =iserror(VLOOKUP(a1,b7:f37,4,FALSE)) -- returns "TRUE", I want a blank cell!!

    I could go on adding more =if statements, but that gets too crazy.

    I can't post a picture, and this message board's formatting screws things up, but you can see the sheet at

    drdumont.webs.com

    In a nutshell, I'd like to display a blank cell if VLOOKUP encounters ANY error.

    Any help would be greatly appreciated, thanks in advance

    -- Doc

  2. #2
    Board Regular
    Join Date
    Oct 2011
    Posts
    698

    Default Re: VLOOKUP - Hide #N/A ERROR

    Try:

    =if(iserror(vlookup(criteria))=true,"",vlookup(criteria))

  3. #3
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,491

    Default Re: VLOOKUP - Hide #N/A ERROR

    IFERROR is a function only available in XL2007 or higher.

    If that returned #NAME? error, then I assume you have 2003 or earlier.

    In 2003, try

    =IF(ISNA(VLOOKUP(a1,b7:f37,4,FALSE)),"",VLOOKUP(a1,b7:f37,4,FALSE))
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

  4. #4
    New Member
    Join Date
    Aug 2007
    Posts
    10

    Default Re: VLOOKUP - Hide #N/A ERROR

    You guys ROCK! Thanks for the info. The IFERROR threw me, the MS help tossed that grenade under my chair with no disclaimer re 2007.
    And after looking at the examples you game me, I now see where I went down the wrong track.
    Your help woiks like a chom, as my old grandpa used to say. Thanks again!

    -- Doc

  5. #5
    New Member
    Join Date
    Aug 2007
    Posts
    10

    Default Re: VLOOKUP - Hide #N/A ERROR

    JONMO1: Hey to Bryan/College Station. Was born in C.S., went to A&M, Boy, has THAT place changed! I was Chief Engineer of WTAW a few years, built KAMU-TV. I owe you a beer at Martin's!
    -- Doc

  6. #6
    MrExcel MVP Jonmo1's Avatar
    Join Date
    Oct 2006
    Location
    Bryan, TX
    Posts
    32,491

    Default Re: VLOOKUP - Hide #N/A ERROR

    Glad to help, thanks for the feedback.

    Yep, just like everywhere else. Always growing..
    Want better/faster responses to your questions?
    Use Excel Jeanie to post samples of your sheet.

    The more we learn, and the better we get at our trade, the easier it becomes to overlook the obvious.

    Life moves pretty fast. If you don't stop and look around once in a while, you could miss it.
    Ferris Bueller A.K.A. John Hughes, 1986

Tags for this Thread

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