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

[JimmyQ]

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$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$63,0),1),VARanks.All!$B$13:B$63,0)+13,"0")&"$63"),0)+(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D14,VARanks.All!D$13$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$63,0),1))

 

[Biff]

See this:

http://tinyurl.com/h6bed

Biff

 

[Leo Heuser]

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$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$63,0),1),VARanks.All!$B$13:B$63,0)+13,"0")&"$63"),0)+(MATCH(INDEX(VARanks.All!$B$13:$B$63,MATCH(D14,VARanks.All!D$13$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$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 D311 with the fill handle (the little square
in the lower right corner of the cell)

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

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

=INDEX(D211,F1)