+ Reply to Thread
Results 1 to 6 of 6

Need help with Index/Match & 0/blank vs actual 0 problem

  1. #1
    Registered User
    Join Date
    10-25-2011
    Location
    Jackson MI
    MS-Off Ver
    Office 2007
    Posts
    18

    Need help with Index/Match & 0/blank vs actual 0 problem

    Good Afternoon,

    I have spent the last hour reading post after post and some have helped but...still no complete answer to the following problem:

    In my standard Index,match statement

    =Iferror(index('Sheet1'!a2:a6,match('sheet2'!b3,'sheet1'!x2:x6,0)),"")

    Any blank cell comes up 0. If I custom format [=0]"";General that renders the cell blank, however, there ARE times I need a 0 to show as entered in Sheet1!a2:a6

    Is there a way to do this other than re-iterating my first statement with an additional if/then statement such as:

    =if(index('Sheet1'!a2:a6,match('sheet2'!b3,'sheet1'!x2:x6,0)=0,0,Iferror(index('Sheet1'!a2:a6,match('sheet2'!b3,'sheet1'!x2:x6,0)),""))

    I would append a copy of this document but it contains PHI and due to HIPAA "blah blah blah"
    This document is going to be a beast as it is (boss hates Access, so I get to force Excel to perform as such) and I am trying to keep calculations to the minimum needed to run as fast as possible.

  2. #2
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Need help with Index/Match & 0/blank vs actual 0 problem

    What are you indexing? Numbers? Words?

    SUMPRODUCT will error out on returning string values, so if you're trying to return numbers you could probably keep it as simple as IFERROR(SUMPRODUCT(),"")
    Make Mom proud: Add to my reputation if I helped out!

    Make the Moderators happy: Mark the Thread as Solved if your question was answered!

  3. #3
    Registered User
    Join Date
    10-25-2011
    Location
    Jackson MI
    MS-Off Ver
    Office 2007
    Posts
    18

    Re: Need help with Index/Match & 0/blank vs actual 0 problem

    Lord, help me stay sane. Perfect and Thank YOU!

  4. #4
    Administrator FDibbins's Avatar
    Join Date
    12-29-2011
    Location
    Duncansville, PA USA
    MS-Off Ver
    Excel 7/10/13/16/365 (PC ver 2310)
    Posts
    52,917

    Re: Need help with Index/Match & 0/blank vs actual 0 problem

    I understand that the data is sensitive, but could you mock something up that will show what you are working with, and what you want?
    1. Use code tags for VBA. [code] Your Code [/code] (or use the # button)
    2. If your question is resolved, mark it SOLVED using the thread tools
    3. Click on the star if you think someone helped you

    Regards
    Ford

  5. #5
    Forum Expert daffodil11's Avatar
    Join Date
    07-11-2013
    Location
    Phoenixville, PA
    MS-Off Ver
    MS Office 2016
    Posts
    4,465

    Re: Need help with Index/Match & 0/blank vs actual 0 problem

    Maybe even:

    =IFERROR(IF(Sheet1$X$2:$E$6=Sheet2!B3,Sheet1$A$2:$A$6),"")
    Last edited by daffodil11; 02-21-2014 at 05:02 PM.

  6. #6
    Registered User
    Join Date
    10-25-2011
    Location
    Jackson MI
    MS-Off Ver
    Office 2007
    Posts
    18

    Re: Need help with Index/Match & 0/blank vs actual 0 problem

    Oh the problem was that it is reporting a number. Got the Iferror part down pat, the problem was blanks were showing as 0. If I added the Custom formatting [=0]"";General it took out all the 0s just fine...BUT...the index/match I am using sometimes (using this to match group and suffix numbers) will report the Suffix number 0 (I have no control over the numbers, BCBS does that) SO at that point I would have a blank, when it was an actual instance of a 0.

    I needed it to show a actual entered 0 when there was a 0, and a blank when it was a blank, without having 3 iterations of the same index/match in the same formula bar.

    Just FYI ( and ducking flying fruit at this point) one worksheet is extended to Column GT. That is the primary data entry sheet driving everything else.

+ Reply to Thread

Thread Information

Users Browsing this Thread

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

Similar Threads

  1. Index/ Match Formula, How to return blank cell as a blank not 0
    By MDResearcher in forum Excel Formulas & Functions
    Replies: 9
    Last Post: 02-24-2016, 08:40 PM
  2. index match, ..... skip result if blank value ...... jump to next match
    By gehawk in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 08-08-2013, 04:42 AM
  3. Replies: 8
    Last Post: 06-29-2012, 10:20 PM
  4. Index Match: Show blank instead of #NA for no match
    By cedarhill in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 06-20-2011, 03:21 PM
  5. If Blank using Match and Index
    By karstens in forum Excel Formulas & Functions
    Replies: 2
    Last Post: 04-29-2008, 11:28 AM

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