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

S

sven

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)
 
R

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

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.
Click to expand...

-- [email protected]
 
S

sven

Ragdyer said:
-- [email protected]
Click to expand...

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?
 
R

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 !
--------------------------------------------------------------------

sven said:
-- [email protected]
Click to expand...

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?
 
Top