How do you make a look-up function look further?

[Guest]

What i can't seem to find out:

For school i made a matrix in which 20 students could score points in
different subjects. Each score was added up, so that each student had a total
score.
Of those total scores i made a Ranking table. I've used excel to put the
scores into assending order and then used the (vertical) look-up function to
find the name that matches the score.
this works perfectly, until there are 2 identical scores. Excel then picks
the first name, for both scores. How can i 'tell' excel to look further in
the directed matrix, if the name found is already used in an earlier cell?
I tried using the If-fuction, but that didn't work.
If anyone knows how to solve this or can be of any help, i would appreciate
it if you replied.
Thanks in advance.

Sven
The netherlands (so also using dutch version, but thats ok)

 

[Ragdyer]

Lets say the scores are in Column A, and the student's name pertaining to
each individual score is in Column B, for a datalist of A1:B20.


The scores are ranked (sorted) in Column F, from F1 to F20.

Enter this *array* formula in G1, and copy down to G20:

=INDEX($B$1:$B$20,LARGE(IF($A$1:$A$20=F1,ROW($A$1:$A$20)),COUNTIF(F1:$F$20,F
1)))

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

In the case of ties, the name listed *first* in the datalist will be
displayed first in the ranking column.

If you might want this reversed, simply change the "Large" function in the
formula to the "Small" function.

 

[CyberTaz]

Depending on how you set up your data range, you may also be interested in
the AutoFilter feature. If you're not familiar with it, go to
Data>Filter>AutoFilter while your active cell is located in your data range.

HTH |:>)

Lets say the scores are in Column A, and the student's name pertaining to
each individual score is in Column B, for a datalist of A1:B20.


The scores are ranked (sorted) in Column F, from F1 to F20.

Enter this *array* formula in G1, and copy down to G20:

=INDEX($B$1:$B$20,LARGE(IF($A$1:$A$20=F1,ROW($A$1:$A$20)),COUNTIF(F1:$F$20,F
1)))

Array formulas must be entered with CSE, <Ctrl> <Shift > <Enter>, instead of
the regular <Enter>, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

In the case of ties, the name listed *first* in the datalist will be
displayed first in the ranking column.

If you might want this reversed, simply change the "Large" function in the
formula to the "Small" function.

-- (e-mail address removed)

 

[Guest]

-- (e-mail address removed)

Ok, I can't seem to make it work proporly and i have no clue what i'm doing
wrong.
I changed the formula into the dutch function and into the
cells/rows/columns that i'm using and it turns out like this:

=INDEX($AC$7:$AC$30;GROOTSTE(ALS($AB$7:$AB$30=C6;RIJ($AB$7:$AB$30));AANTAL.ALS(C6:$C$26;C6)))

When i add it using CSE, i get the array thingys around it, but it doesn't
find the correct name. It also gives several error cells when i copy it down
to the entire column. So i must be doing something wrong.. but what?

 

[RagDyeR]

I would suggest that you make a small test datalist on a new sheet, where
you key in the entries yourself, and then copy to the ranking column.

This would insure that the data is *exactly* the same in all cases.
That there were perhaps, no leading or trailing spaces, or that all numeric
data is truly numeric, and not text that looks like numbers, or a mixture of
text and numbers.

There are numerous reasons why formulas fail, and the data itself can be the
reason, as well as the formula.
--

Regards,

RD
--------------------------------------------------------------------
Please keep all correspondence within the Group, so all may benefit !
--------------------------------------------------------------------

-- (e-mail address removed)

Ok, I can't seem to make it work proporly and i have no clue what i'm doing
wrong.
I changed the formula into the dutch function and into the
cells/rows/columns that i'm using and it turns out like this:

=INDEX($AC$7:$AC$30;GROOTSTE(ALS($AB$7:$AB$30=C6;RIJ($AB$7:$AB$30));AANTAL.A
LS(C6:$C$26;C6)))

When i add it using CSE, i get the array thingys around it, but it doesn't
find the correct name. It also gives several error cells when i copy it down
to the entire column. So i must be doing something wrong.. but what?