Results 1 to 6 of 6
  1. #1
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts

    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. #2
    Super Moderator RetiredGeek's Avatar
    Join Date
    Mar 2004
    Location
    Manning, South Carolina
    Posts
    9,436
    Thanks
    372
    Thanked 1,457 Times in 1,326 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

    PowerShell & VBA Rule!

    My Systems: Desktop Specs
    Laptop Specs

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

    XPDiHard (2014-09-01)

  4. #3
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    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 19:27.

  5. #4
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 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 21:23.

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

    XPDiHard (2014-09-01)

  7. #5
    Gold Lounger Maudibe's Avatar
    Join Date
    Aug 2010
    Location
    Pa, USA
    Posts
    2,638
    Thanks
    115
    Thanked 650 Times in 592 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))

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

    XPDiHard (2014-09-01)

  9. #6
    3 Star Lounger
    Join Date
    Nov 2011
    Location
    Australia
    Posts
    221
    Thanks
    80
    Thanked 3 Times in 2 Posts
    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 02: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
  •