Results 1 to 6 of 6
  1. #1
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    154
    Thanks
    34
    Thanked 2 Times in 1 Post

    Index match formula for point scoring

    I have tried many combinations, googled examples etc to get this INDEX MATCH formula to work right.

    The problem area is in the range of C56:C80
    All I get is 0's along the entire Range, the MATCH needs to correspond between ranges C30 : D54

    Appreciate if someone can get this to work right


    Thanks
    Attached Files Attached Files

  2. Subscribe to our Windows Secrets Newsletter - It's Free!

    Get our unique weekly Newsletter with tips and techniques, how to's and critical updates on Windows 7, Windows 8, Windows XP, Firefox, Internet Explorer, Google, etc. Join our 480,000 subscribers!

    Excel 2013: The Missing Manual

    + Get this BONUS — free!

    Get the most of Excel! Learn about new features, basics of creating a new spreadsheet and using the infamous Ribbon in the first chapter of Excel 2013: The Missing Manual - Subscribe and download Chapter 1 for free!

  3. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    6,051
    Thanks
    195
    Thanked 758 Times in 694 Posts
    XPD,

    I'm not sure what you are trying to accomplish but I think part of the problem is you're matching on the value (cell C58) you are trying to change!
    xpd.JPG
    HTH
    May the Forces of good computing be with you!

    RG

    VBA Rules!

    My Systems: Desktop Specs
    Laptop Specs


  4. The Following User Says Thank You to RetiredGeek For This Useful Post:

    XPDiHard (2014-09-01)

  5. #3
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    154
    Thanks
    34
    Thanked 2 Times in 1 Post
    Thanks RG,
    firstly I just noticed a small oversight in one of the formula range.
    between D56 and D80
    was
    =IFERROR(SMALL($D$30:$D$51,ROW(2:2)),"")
    Change to
    =IFERROR(SMALL($D$30:$D$54,ROW(2:2)),"")
    Please correct this.
    -------------------,

    However,
    What I'm after is to reference the following;

    Formula used: =IF(C4="","",(IF(C4<0,4,0)))
    This Formula gives a value of 4 points to any cell range that is below 0.
    SOURCE SCORING

    4 7
    4 4
    4 12
    4 9
    4 11
    4 5
    0 2
    0 6
    0 8
    0 10
    0 3
    0 1

    So when I Ascend using Formula: =IFERROR(SMALL($D$30:$D$54,ROW(2:2)),""),
    It Ascend as required.

    BUT,
    When I use this formula to MATCH the Ascendings, =IFERROR(INDEX($D$56:$D$80,MATCH(C56,C$30:C$54,0)) ,"")
    or vary the INDEX references from D to C ranges, I don't get the 4's at all in the example provided.

    The Red highlighted cells is there to show for easy reference in the example.
    -------------

    Ideally it is supposed to end up Ascended and the relative scores, (4) without the 0's.
    SOURCE AND SCORING SORTED
    2
    3
    4 4
    4 5
    6
    4 7
    8
    4 9
    10
    4 11
    4 12














    -------------------------------
    Last edited by XPDiHard; 2014-08-31 at 18:27.

  6. #4
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    XP,

    in cell C56, try this formula and copy down:
    Code:
    =INDEX(C$30:D$54,MATCH(D56,D$30:D$54,0),1)
    It will return the matching 0/4 from the Source Scoring section

    You can wrap it in your IFERROR() if it is what you want.

    0 2
    0 3
    4 4
    4 5
    0 6
    4 7
    0 8
    4 9
    0 10
    4 11
    4 12

    HTH,
    Maud
    Last edited by Maudibe; 2014-08-31 at 20:23.

  7. The Following User Says Thank You to Maudibe For This Useful Post:

    XPDiHard (2014-09-01)

  8. #5
    5 Star Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    1,104
    Thanks
    39
    Thanked 194 Times in 181 Posts
    If you do not want the zeros to show, Place in C56 then copy down:
    Code:
    =if(INDEX(C$30:D$54,MATCH(D56,D$30:D$54,0),1)=0,"",INDEX(C$30:D$54,MATCH(D56,D$30:D$54,0),1))

  9. The Following User Says Thank You to Maudibe For This Useful Post:

    XPDiHard (2014-09-01)

  10. #6
    2 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    154
    Thanks
    34
    Thanked 2 Times in 1 Post
    Thanks Maud
    Now I "see" it, it was this part I missed C$30 : D$54,
    It crosses over between C & D


    I use the latter so the zeros don't show
    Code:
    =if(INDEX(C$30:D$54,MATCH(D56,D$30:D$54,0),1)=0,"",INDEX(C$30:D$54,MATCH(D56,D$30:D$54,0),1))
    Last edited by XPDiHard; 2014-09-01 at 01:24.

Posting Permissions

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