One way to get around duplicates in this type of setup
set up a helper column
if your comparison data is in column A
enter in helper column
=rank(A1,A:A,1)+row()/100000
this will give a unique number for matches
Note that the higher row numbers will be ranked higher in this case.
if you want lower row numbers to be selected first, use a minus row...
If you have an alternate tie breaker it can also be used
(I have had complicated enough tie breakers that I had to go to 6 layers of
rank to stay within the 15 digit limit for Excel.
"Richard" wrote:
> I am using INDEX, MATCH & LARGE function to find a customer reference for top
> 3 values in a specific column in an array of data;
>
> Basically I use LARGE (1...3) to find the top five values, and use the MATCH
> function to find the row reference in the array.
>
> INDEX function then points to the array Row and Col for the customer
> representing that value.
>
> My problem is that the Match function always finds the first value if there
> are multiple equal values in the column. So if 2 values are equal, then
> match always picks the first of these values, even though Large 1 & Large 2
> will find both values. How do I create a pointer reference to the second
> value (as selected by the Large function)
>
> CU_Array = $A$1:$C$5
> REF = 1,2,3 can be one of 3 values
> A B C
> 1 Cust_Row1 100 200
> 2 Cust_Row2 67 150
> 3 Cust_Row3 100 125
> 4 Cust_Row4 156 200
> 5 Cust_Row5 120 100
>
> The formula I am using is
> =INDEX(CU_Array,MATCH(LARGE(c1:c5,REF),c1:c5,0),0)
>
> Anyone have any suggestions...
>
> Thanks Richard
Bookmarks