RE: Using Match function with duplicate values in an array
- From: "Richard" <Richard@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Thu, 30 Jun 2005 14:20:02 -0700
Hi BJ, many many thanks this has served the purpose and seems to have solved
the problem for multiple values
It woudl be nice if I could do this without having to create the helper
column, but its not too much inconvenience.
Thank you very much -- richard 06/30
--
"bj" wrote:
> 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
.
- Prev by Date: Re: Nested If statement
- Next by Date: Re: SUMIF
- Previous by thread: Re: Nested If statement
- Next by thread: If statements
- Index(es):
Relevant Pages
|
