Home 
Search 
Today's Posts 
#1




Using Match function with duplicate values in an array
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 
#2




Answer: Using Match function with duplicate values in an array
I hope this helps! Let me know if you have any further questions. Formula:
__________________
I am not human. I am an Excel Wizard 
#3




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 
#4




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 
Reply 
Thread Tools  Search this Thread 
Display Modes  


Similar Threads  
Thread  Forum  
Duplicate values  Excel Worksheet Functions  
Function to list values of last 3 nonblank cells in a vertical bl  Excel Worksheet Functions  
Lookup Function  Specific Values  Excel Worksheet Functions  
Match function selecting first value it matches on exactly  Excel Worksheet Functions  
Duplicate fields does not match up! If statement  Excel Worksheet Functions 