'Match' a range, skip a predefined cell

[WaqasTariq]

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:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]A[/TD]
[TD="align: center"]B[/TD]
[TD="align: center"]C[/TD]
[TD="align: center"]D[/TD]
[TD="align: center"]E[/TD]
[TD="align: center"]F[/TD]
[TD="align: center"]G[/TD]
[TD="align: center"]H[/TD]
[TD="align: center"]I[/TD]
[TD="align: center"]J[/TD]
[/TR]
[TR]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, width: 33, align: right"]0.22[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, width: 33, align: right"]0.12[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, width: 33, align: right"]0.19[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, width: 33, align: right"]0.22[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, width: 33, align: right"]0.32[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, width: 33, align: right"]0.27[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, width: 33, align: right"]0.25[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, align: right"]0.95[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, align: right"]0.95[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 33"]
<tbody>[TR]
[TD="class: xl66, align: right"]0.95[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

Formula I am using:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD]=MATCH(LARGE(A1:J1,1),A1:J1,0)[/TD]
[TD]=MATCH(LARGE(A1:J1,2),A1:J1,0)[/TD]
[TD]=MATCH(LARGE(A1:J1,3),A1:J1,0)[/TD]
[/TR]
</tbody>[/TABLE]

Result:

[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]8[/TD]
[TD="align: center"]8[/TD]
[/TR]
</tbody>[/TABLE]

Result I want:
[TABLE="width: 500"]
<tbody>[TR]
[TD="align: center"]L[/TD]
[TD="align: center"]M[/TD]
[TD="align: center"]N[/TD]
[/TR]
[TR]
[TD="align: center"]8[/TD]
[TD="align: center"]9[/TD]
[TD="align: center"]10[/TD]
[/TR]
</tbody>[/TABLE]

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

I will appreciate any help!

 

[Kamolga]

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

=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...

​

​

​

​

 

[Fluff]

Another option using helper columns

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R
1	0.22	0.12	0.19	0.22	0.32	0.27	0.25	0.95	0.95	0.95		0.95	0.95	0.95		8	9	10
2	0.22	0.12	0.95	0.22	0.32	0.27	0.25	0.95	0.94	0.95		0.95	0.95	0.95		3	8	10
3	0.22	0.12	0.19	0.95	0.32	0.27	0.25	0.93	0.94	0.95		0.95	0.95	0.94		4	10	9
Norfolk

Cell Formulas
Range	Formula
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

 

[WaqasTariq]

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.

 

[Fluff]

You're welcome & thanks for the feedback

 

[Kamolga]

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.