MATCH() and duplicate values -- is there a workaround?

I am compiling a Top 10 ranking from a list of companies with numerical
scores attached to each. I am using LARGE() to order the list, then MATCH()
to locate the position of each of the top 10 values, then INDEX() to read the
company name.

Problem is, there are duplicate values in the LARGE() -- i.e. two companies
may have the same score -- e.g. 2. The MATCH() gets "stuck" on row in which
the value occurs, and keeps returning the same company name.

As a workaround, my formula tries to determine whether the company name has
already occurred in the Top 10 list and, if so, advance the beginning of the
MATCH() range to the row following that in which the company name was found
in the search array. I.e. if ABC Enterprises has already shown up in the Top
10 list, go to the next row in the search array and start the next MATCH()
from there.

However, the formula is very unwieldy (see below) and Excel has difficulty
parsing it -- it appears to work correctly in one row, but generates error
values in others.

This would all be unncessary if the MATCH() function could deal with
duplicates -- i.e. if it has already MATCHed a value in an array, move to the
next one.

Pivot Tables and other manual solutions are not viable -- this has to be
automated.

Any ideas anyone?


IF(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARanks.All!D$13:D$63,0),1),C$12:C$17,0),INDEX(VARanks.All!B$13:B$63,MATCH(D16,INDIRECT("VARanks.All!D$"&TEXT(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D15,VARanks.All!D$13:D$63,0),1),VARanks.All!$B$13:B$63,0)+13,"0")&":D$63"),0)+(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D14,VARanks.All!D$13:D$63,0),1),VARanks.All!$B$13:B$63,0)+13-1),1),INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARanks.All!D$13:D$63,0),1))

See this:

http://tinyurl.com/h6bed

Biff

"JimmyQ" <JimmyQ@discussions.microsoft.com> wrote in message
news:DF38A23A-059B-49A2-B342-6A6478334E33@microsoft.com...
>I am compiling a Top 10 ranking from a list of companies with numerical
> scores attached to each. I am using LARGE() to order the list, then
> MATCH()
> to locate the position of each of the top 10 values, then INDEX() to read
> the
> company name.
>
> Problem is, there are duplicate values in the LARGE() -- i.e. two
> companies
> may have the same score -- e.g. 2. The MATCH() gets "stuck" on row in
> which
> the value occurs, and keeps returning the same company name.
>
> As a workaround, my formula tries to determine whether the company name
> has
> already occurred in the Top 10 list and, if so, advance the beginning of
> the
> MATCH() range to the row following that in which the company name was
> found
> in the search array. I.e. if ABC Enterprises has already shown up in the
> Top
> 10 list, go to the next row in the search array and start the next MATCH()
> from there.
>
> However, the formula is very unwieldy (see below) and Excel has difficulty
> parsing it -- it appears to work correctly in one row, but generates error
> values in others.
>
> This would all be unncessary if the MATCH() function could deal with
> duplicates -- i.e. if it has already MATCHed a value in an array, move to
> the
> next one.
>
> Pivot Tables and other manual solutions are not viable -- this has to be
> automated.
>
> Any ideas anyone?
>
>
> IF(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARanks.All!D$13:D$63,0),1),C$12:C$17,0),INDEX(VARanks.All!B$13:B$63,MATCH(D16,INDIRECT("VARanks.All!D$"&TEXT(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D15,VARanks.All!D$13:D$63,0),1),VARanks.All!$B$13:B$63,0)+13,"0")&":D$63"),0)+(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D14,VARanks.All!D$13:D$63,0),1),VARanks.All!$B$13:B$63,0)+13-1),1),INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARanks.All!D$13:D$63,0),1))
>

"JimmyQ" <JimmyQ@discussions.microsoft.com> skrev i en meddelelse
news:DF38A23A-059B-49A2-B342-6A6478334E33@microsoft.com...
>I am compiling a Top 10 ranking from a list of companies with numerical
> scores attached to each. I am using LARGE() to order the list, then
> MATCH()
> to locate the position of each of the top 10 values, then INDEX() to read
> the
> company name.
>
> Problem is, there are duplicate values in the LARGE() -- i.e. two
> companies
> may have the same score -- e.g. 2. The MATCH() gets "stuck" on row in
> which
> the value occurs, and keeps returning the same company name.
>
> As a workaround, my formula tries to determine whether the company name
> has
> already occurred in the Top 10 list and, if so, advance the beginning of
> the
> MATCH() range to the row following that in which the company name was
> found
> in the search array. I.e. if ABC Enterprises has already shown up in the
> Top
> 10 list, go to the next row in the search array and start the next MATCH()
> from there.
>
> However, the formula is very unwieldy (see below) and Excel has difficulty
> parsing it -- it appears to work correctly in one row, but generates error
> values in others.
>
> This would all be unncessary if the MATCH() function could deal with
> duplicates -- i.e. if it has already MATCHed a value in an array, move to
> the
> next one.
>
> Pivot Tables and other manual solutions are not viable -- this has to be
> automated.
>
> Any ideas anyone?
>
>
> IF(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARanks.All!D$13:D$63,0),1),C$12:C$17,0),INDEX(VARanks.All!B$13:B$63,MATCH(D16,INDIRECT("VARanks.All!D$"&TEXT(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D15,VARanks.All!D$13:D$63,0),1),VARanks.All!$B$13:B$63,0)+13,"0")&":D$63"),0)+(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D14,VARanks.All!D$13:D$63,0),1),VARanks.All!$B$13:B$63,0)+13-1),1),INDEX(VARanks.All!$B$13:$B$63,MATCH(D10,VARanks.All!D$13:D$63,0),1))
>


Jimmy

Assuming company names in A2:A27, scores in B2:B27 and
top 10 scores in C2:C11, here's one way to solve it:

In D2 this array formula:

=INDEX($A$2:$A$27,SMALL(IF(C2=$B$2:$B$27,ROW($B$2:$B$27)-ROW($B$2)+1),COUNTIF($C$2:C2,C2)))

To be entered with <Shift><Ctrl><Enter>, also if edited later
Copy D2 down to D3:D11 with the fill handle (the little square
in the lower right corner of the cell)

D2:D11 now contains the names of the top 10 companies
(duplicates included).

To get the company name for a specific ranking try this setup:

In e.g. F1 enter the position in the top 10 range.

In F2 this array formula:

=INDEX(A2:A27,SMALL(IF(INDEX(C2:C11,F1)=B2:B27,ROW(B2:B27)-ROW(B2)+1),COUNTIF(OFFSET(C2,,,F1),INDEX(C2:C11,F1))))

again to be entered with <Shift><Ctrl><Enter>
As you can see, this solution doesn't depend on a created list in D2:D11.

If you want that list anyway, the easy way to get the company name
is of course

=INDEX(D2:D11,F1)


--
Best regards
Leo Heuser

Followup to newsgroup only please.