index, match

Question

can anyone help me to get this right please:

data range:A1 to A19 (containing student names) and B1 to B19 (containing scores for a test).

from B21 to B39, i use this formula to sort scores from large to small =LARGE($B$1:$B$20,1...)

from A1 to A39 i enter this formula =INDEX($A$1:$B$19,MATCH(B21,$B$1:$B$19,0),1)

it works fine for the students that have at least got a score but for those that scored NIL, it just repeats a single student only all the way down.

i have a number of tests to run the same process above and i need a formula that can at least give me the ones that have nil in some kind of order or at least appear once anywhere where score is NIL.

Please help.

Answer 1

Thanks for your help guys. Steve, why is it that I cannot place your last two formulas anywhere? I have tried moving them or inserting extra rows on top and between but it gets the data distorted. I have tried to modify references but am still getting lost. How can these two be made more flexible?

Thanks,

Nick.

Answer 2

To make life a bit easier in C1 enter

=20-COUNTIF($B$1:$B$19,"<"&B1)-COUNTIF(B1:$B$19,B1)

and copy down to C19, this will give you a rank of scores High to Low. Where there is a duplicate score the first in the list is ranked higher than the second, but all will be included.

Then in A21 you can enter a formula that references this list, enter

=INDIRECT("A"&MATCH(ROW()-20,$C$1:$C$19,0))

Which will form a reference to the name in column "A" against the row number for the highest score which it finds in "C" copy this down to A39. And in B21 enter

=INDIRECT("B"&MATCH(ROW()-20,$C$1:$C$19,0))

Which will match the scores to the names.

These formulas are specific to the data you gave as an example so if there are more than 19 students it will have to be adapted. But that is just a matter of changing a few numbers. And you can always hide column "C" if you don't want to see it on your sheet.

Just as a note in the first formula change "<" to ">" to sort the list low to high

Regards

 Steve

Answer 3

If you're comfortable with using an array-entered formula, you could use this in A21:

=INDEX($A$1:$A$19,SMALL(IF($B$1:$B$19=$B21,ROW($B$1:$B$19)),

ROW(1:1)-COUNTIF($B$21:$B21,"<>"&$B21)))

---

Steve D.

Answer 4

Hi Nick,

You would find that the problem exists for any score that is repeated.

Instead of putting constants into the LARGE(), try this in B21:

=LARGE($B$1:$B$19,ROW(1:1))

In A21 use this:

=INDEX($A$1:$A$19,SMALL(INDEX(($B$1:$B$19=$B21)*ROW($B$1:$B$19),),

COUNTIF($B$1:$B$19,"<>"&$B21)+ROW(1:1)-COUNTIF($B$21:$B21,"<>"&$B21)))

Copy A21:B21 down as far as A39:B39

HTH

---

Steve D.

Answer 5

I don't know what your first formula is but try this:

=IF(ISERR(LARGE($B$1:$B$19,ROW(A1))),"",LARGE($B$1:$B$19,ROW(A1)))

For the second one use the Array** formula:

=IF(D1="",INDEX($A$1:$A$19,SMALL(($B$1:$B$19="")*ROW($B$1:$B$19),ROW(A1))),INDEX($A$1:$A$19,MATCH(D1,$B$1:$B$19,0)))

**Array to enter this formula press Shift+Ctrl+Enter instead of Enter.

---

If this answer solves your problem, please check Mark as Answered. If this answer helps, please click the Vote as Helpful button. Cheers, Shane Devenshire