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

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

[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.
--
HTH,

RD

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

"sven" <[email protected]> wrote in message
news:[email protected]...
> 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)
>

[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 |:>)


On 3/13/05 6:08 PM, in article [email protected],
"Ragdyer" wrote:

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

-- [email protected]

[Bryan Hessey]

I assume that column A contains the rankings, and column B the names (sorted in column A order), you could bring all the relavant names to the first occurance with column D set to: =IF(A1=A2,B1&" "&D2,B1) (copy this down the sheet)
You would then Lookup just one occurance of each score to receive all names.

[sven]

> "Ragdyer" wrote:
>
> > 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.
>
> -- [email protected]
>

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

"sven" <[email protected]> wrote in message
news:[email protected]...
> "Ragdyer" wrote:
>
> > 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.
>
> -- [email protected]
>

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?

[Bryan Hessey]

Not being too bright, I can get most of this formula working, (as separate portions in help-columns) but not all.
The 'Large' function (as used) gives a #NUM! where only a single occurance of the score occurs in the table.
I presume that most of the scores used would be single entries, and would thus present a large problem.
(ps, I find this formula useful for other purposes)
Is there any simple method to deal with the single occurance items?