+ Reply to Thread
Results 1 to 3 of 3

Displaying empty cells and not #N/A when missing information in a formula

  1. #1
    Registered User
    Join Date
    10-20-2012
    Location
    salinas, ca
    MS-Off Ver
    Excel 2010
    Posts
    25

    Smile Displaying empty cells and not #N/A when missing information in a formula

    Hello everyone,

    I need some help with this formula: =INDEX(Item_Description,MATCH(B14,Item_Code,0))
    Whent the item number is entered in cell B14, the formula displays the item description in cell C14 (this is where the fomula resides); but when there is no item number in the first cell the second cell displays #N/A.

    Is there a way to have the second cell display nothing until there is correct data in the first cell? I've tried searching the posts for something that could help but nothing has worked so far. The spreadsheet will be printed once the information is entered, but there are a lot of cells with #N/A since there are empty item number cells... and I would like to clean up the sheet before it gets to the printer.

    Please let me know if anyone needs more info on this one... and thank you in advance for any help anyone can provide.

  2. #2
    Forum Guru Pete_UK's Avatar
    Join Date
    12-31-2011
    Location
    Warrington, England
    MS-Off Ver
    Excel 2007
    Posts
    19,830

    Re: Displaying empty cells and not #N/A when missing information in a formula

    You do it like this:

    =IFERROR(INDEX(Item_Description,MATCH(B14,Item_Code,0)),"")

    but if you need to be compatible with XL2003 or earlier, then you have to do this:

    =IF(ISNA(MATCH(B14,Item_Code,0)),"",INDEX(Item_Description,MATCH(B14,Item_Code,0))

    or this:

    =IF(COUNTIF(Item_code,B14)>0,INDEX(Item_Description,MATCH(B14,Item_Code,0)),"")

    Hope this helps.

    Pete

  3. #3
    Registered User
    Join Date
    10-20-2012
    Location
    salinas, ca
    MS-Off Ver
    Excel 2010
    Posts
    25

    Re: Displaying empty cells and not #N/A when missing information in a formula

    Thanks for the help, Pete... your suggestion worked perfectly!!

    I'm so glad I found this forum... there's always someone who knows the perfect solution to a problem!

    Thanks again...

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Bookmarks

Posting Permissions

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

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1