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

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:
`=IF(COUNTIF(A1:J1,LARGE(A1:J1,1))>1,MATCH(LARGE(A1:J1,1),INDIRECT(SUBSTITUTE(ADDRESS(1,MATCH(LARGE(A1:J1,1),A1:J1,0)+1,4),1,"")&"1:J1"),0)+MATCH(LARGE(A1:J1,1),A1:J1,0),MATCH(LARGE(A1:J1,2),A1:J1,0))`
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...

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

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.

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

You're welcome & thanks for the feedback

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.

## User Tag List

#### Posting Permissions

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