Results 1 to 4 of 4

Thread: Help With Index/Match Formula!!

  1. #1

    Exclamation Help With Index/Match Formula!!



    Register for a FREE account, and/
    or Log in to avoid these ads!

    I'm tracking the percent complete (PPC) for individuals and their assigned tasks. I have a problem where index/match returns a "0%" complete if the cell is blank in between cells that have a value. 0 implies that this person failed alPPC Progress Data Organization.xlsl of their tasks, when in truth, they just weren't assigned any new tasks. Since I use this data in a graph it would be very miss interpretive if the "0%" exists in the data. however, when there is no data in the cell to the right of it "" will show up. I guess a blank and a 0% show up the same in a graph, but if one is looking at the raw data a 0% is misleading compared to an empty cell.

    Attached is the spreadsheet im refering to, can someone please help me out??

  2. #2
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    If 0% is actually acceptable result sometimes, then best to use an IF...

    e.g.

    =IF(INDEX(I$5:I$25,MATCH($F$3,$F5:$F25,0))="","",INDEX(I$5:I$25,MATCH($F$3,$F5:$F25,0)))


  3. #3
    Quote Originally Posted by NBVC View Post
    If 0% is actually acceptable result sometimes, then best to use an IF...

    e.g.

    =IF(INDEX(I$5:I$25,MATCH($F$3,$F5:$F25,0))="","",INDEX(I$5:I$25,MATCH($F$3,$F5:$F25,0)))

    0% is an acceptable answer sometimes so that formula does appear to work. could explain why there is a = and "," in the middle? That part of your formula is a bit confusing to me. Thank you!

  4. #4
    Super Moderator NBVC's Avatar
    Join Date
    May 2011
    Location
    Mississauga, Canada
    Posts
    1,436
    Articles
    0
    Excel Version
    Excel 2016
    If the result of the INDEX function is a null then return a null else return the INDEX function result. The double quote set signifies a null.


Posting Permissions

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