# Thread: Index match formula for point scoring

1. ## 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

2. 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

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

XPDiHard (2014-09-01)

4. 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)),"")
-------------------,

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

-------------------------------

5. 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

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

XPDiHard (2014-09-01)

7. 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. 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))`

#### Posting Permissions

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