'Match' a range, skip a predefined cell

WaqasTariq

Board Regular
Joined
Jun 26, 2012
Messages
54
Office Version
  1. 365
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:

ABCDEFGHIJ
0.22

<tbody>
</tbody>
0.12

<tbody>
</tbody>
0.19

<tbody>
</tbody>
0.22

<tbody>
</tbody>
0.32

<tbody>
</tbody>
0.27

<tbody>
</tbody>
0.25

<tbody>
</tbody>
0.95

<tbody>
</tbody>
0.95

<tbody>
</tbody>
0.95

<tbody>
</tbody>

<tbody>
</tbody>

Formula I am using:

LMN
=MATCH(LARGE(A1:J1,1),A1:J1,0)=MATCH(LARGE(A1:J1,2),A1:J1,0)=MATCH(LARGE(A1:J1,3),A1:J1,0)

<tbody>
</tbody>

Result:

LMN
888

<tbody>
</tbody>

Result I want:
LMN
8910

<tbody>
</tbody>

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

I will appreciate any help!
 

Excel Facts

Why does 9 mean SUM in SUBTOTAL?
It is because Sum is the 9th alphabetically in Average, Count, CountA, Max, Min, Product, StDev.S, StDev.P, Sum, VAR.S, VAR.P.
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...

 
Last edited:
Upvote 0
Another option using helper columns


Book1
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
Cell Formulas
RangeFormula
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
 
Upvote 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...
@Kamolga Thank you for the help. Yes, I ended up doing a for loop in VBA.

Drag both formulae to the right & down
Thank you @Fluff, I ended up using VBA, but will keep this as reference for myself.
 
Upvote 0
You're welcome & thanks for the feedback
 
Upvote 0
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.
 
Upvote 0

Forum statistics

Threads
1,213,494
Messages
6,113,986
Members
448,538
Latest member
alex78

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top