Page 1 of 2 12 LastLast
Results 1 to 10 of 11

Thread: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

  1. #1
    New Member
    Join Date
    Jun 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    Trying to return a blank instead of a #N/A or 0 with INDEX and MATCH. Formula Below...

    =INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0))


    Thanks in advance for your assistance and time.

    Regards

    Richard T.

  2. #2
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    Hi & welcome to MrExcel.
    How about
    =IFERROR(INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0)),"")
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  3. #3
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    If the formula only returns the #NA error value then =IFERROR(
    INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0)),"") returns the value, or a null text string "" if the result is an error.
    Otherwise you'll need an IF function as well:
    =IFERROR(IF(
    INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0))=0,"",
    INDEX(Data[GC],MATCH([Product Key],Data[Product Code],0))),"")




    Helping you to Excel

  4. #4
    New Member
    Join Date
    Jun 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    Fluff's suggestion removed the #N/A's but still had the 0.

    ClaireS, your suggestion HOOKED IT UP!!!

    Thanks!!!

  5. #5
    New Member
    Join Date
    Jun 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    What if I just have 0's

    =INDEX(Data_InFlight[Hard Cost Vendor],MATCH([Product Key],Data_InFlight[Product Key],0))

  6. #6
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    Can you please explain?
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  7. #7
    New Member
    Join Date
    Jun 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    Claires removed the 0's and the #N/A's. your first suggestion removed the #N/A's. I have some columns that are just returning 0's.

  8. #8
    Board Regular
    Join Date
    Jul 2013
    Location
    Essex, UK
    Posts
    128
    Post Thanks / Like
    Mentioned
    2 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    Thanks, glad I was able to help.

    Helping you to Excel

  9. #9
    MrExcel MVP
    Moderator
    Fluff's Avatar
    Join Date
    Jun 2014
    Location
    Chippenham
    Posts
    28,203
    Post Thanks / Like
    Mentioned
    470 Post(s)
    Tagged
    47 Thread(s)

    Default Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    In that case use ClaireS' formula
    - Posting Data try one of these tools
    - Posting guidelines, forum rules and terms of use
    - Read the FAQs

    Running Office 365 on Win 10

  10. #10
    New Member
    Join Date
    Jun 2019
    Posts
    7
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: Return a Blank instead of #N/A and/or 0 with INDEX/MATCH

    It worked. I must have done something wrong at first, cause it was removing some of the info.... Thanks Guys!!!

Some videos you may like

User Tag List

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
  •