Thread: 'Match' a range, skip a predefined cell Thanks:  1 Post #5325067 (1) Likes:  2 Post #5325067 (1)Post #5325468 (1)

1. 'Match' a range, skip a predefined cell

I am trying to find where the top 3 values in a range are stored. That part is done, however, if one or more of the top 3 have the same value, I only get the same cell locations and not the top 3.

Here is how my data looks like:

A B C D E F G H I J
 0.22
 0.12
 0.19
 0.22
 0.32
 0.27
 0.25
 0.95
 0.95
 0.95

Formula I am using:

 L M N =MATCH(LARGE(A1:J1,1),A1:J1,0) =MATCH(LARGE(A1:J1,2),A1:J1,0) =MATCH(LARGE(A1:J1,3),A1:J1,0)

Result:

 L M N 8 8 8

Result I want:
 L M N 8 9 10

Where 8 is column H, 9 is column I and 10 is column J.

I will appreciate any help!  Reply With Quote

2. Re: 'Match' a range, skip a predefined cell

For M,
I reduce range if countif first value is bigger than 1. I therefore look for large 1 in column 8+1 to 10, so I-J (I use substitute to get from 8 to H) and indirect to have range I1:J1

Code:
Would be much easier to solve through VBA for N instead of all the ifs (if count value 1 is 3,if value 1 is 2, if value 1 is 1 and value 2 is >=2, if value1 is 1 and value 2 is 1...  Reply With Quote

3. Re: 'Match' a range, skip a predefined cell

Another option using helper columns

ABCDEFGHIJKLMNOPQR
10.220.120.190.220.320.270.250.950.950.950.950.950.958910
20.220.120.950.220.320.270.250.950.940.950.950.950.953810
30.220.120.190.950.320.270.250.930.940.950.950.950.944109

Norfolk

Worksheet Formulas
CellFormula
L1=LARGE(\$A1:\$J1,COLUMN(A1))
P1=AGGREGATE(15,6,(COLUMN(\$A1:\$J1)-COLUMN(\$A1)+1)/(\$A1:\$J1=L1),COUNTIF(\$L1:L1,L1))

Drag both formulae to the right & down  Reply With Quote

4. Re: 'Match' a range, skip a predefined cell Originally Posted by Kamolga Would be much easier to solve through VBA for N instead of all the ifs (if count value 1 is 3,if value 1 is 2, if value 1 is 1 and value 2 is >=2, if value1 is 1 and value 2 is 1...
@Kamolga Thank you for the help. Yes, I ended up doing a for loop in VBA. Originally Posted by Fluff Drag both formulae to the right & down
Thank you @Fluff, I ended up using VBA, but will keep this as reference for myself.  Reply With Quote

5. Re: 'Match' a range, skip a predefined cell

You're welcome & thanks for the feedback  Reply With Quote

6. Re: 'Match' a range, skip a predefined cell

In VBA, once you have the values, instead of looping 3 times through the range (in this example it does not mater because it is small but maybe you will increase it), you can use Range.findnext to get the second position.  Reply With Quote

User Tag List

Tags for this Thread

array, column, large, match, top  Posting Permissions

• You may not post new threads
• You may not post replies
• You may not post attachments
• You may not edit your posts
•