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,V ARanks.All!D$13:D$63,0),1),C$12:C$17,0),INDEX(VARa nks.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,VARa nks.All!D$13:D$63,0),1))