Concatenate, INDEX, MATCH, LARGE.

[S.H.A.D.O.]

I would like to Concatenate the two largest numbers in cells M7:M55 that reflect the numbers in cell B7:B55.

This achieves the correct result for the first number, but I can't seem to be able to get the concatenate to work.

=INDEX(B7:B55,MATCH(LARGE(M7:M55,1),M7:M55,0))

So basically, whatever the two largest numbers in M are, it returns the values in B.

I know I could use:-

=INDEX(B7:B55,MATCH(LARGE(M7:M55,1),M7:M55,0))&"   "&INDEX(B7:B55,MATCH(LARGE(M7:M55,2),M7:M55,0))

but I thought there might be an easier way.

Thanks in advance.

 

[S.H.A.D.O.]

OK, I have adapted the formula and got it working:-

=CONCATENATE(
"1 = ",INDEX(B7:B55,MATCH(LARGE(M7:M55,1),M7:M55,0)),
": 2 = ",INDEX(B7:B55,MATCH(LARGE(M7:M55,1),M7:M55,0)),",",INDEX(B7:B55,MATCH(LARGE(M7:M55,2),M7:M55,0)),
": 3 = ",INDEX(B7:B55,MATCH(LARGE(M7:M55,1),M7:M55,0)),",",INDEX(B7:B55,MATCH(LARGE(M7:M55,2),M7:M55,0)),",",INDEX(B7:B55,MATCH(LARGE(M7:M55,3),M7:M55,0)))

The thing is, if there are two numbers with the same value for say LARGE 2 & 3 it just puts the LARGE 2 twice.
Is there an easy way round this.
I did try multiplying the LARGE numbers by 10, 100 & 1000 respectively to sort them but couldn't seem to get it to work.
I will continue to have a play around with the formula.

Thanks in advance.

 

[S.H.A.D.O.]

I got there in the end!

What I did was to setup a helper column and used RANK on column M in the Main Statistics.

=RANK(M7,$M$7:$M$55)+COUNTIF($M$7:M7,M7)-1

...and copied it down.
I then changed the formula to use SMALL instead of LARGE.

For anybody that is interested here is the formula:-

=CONCATENATE(
"  1 = ",INDEX('Main Statistics'!B7:B55,MATCH(SMALL('Main Statistics'!S7:S55,1),'Main Statistics'!S7:S55,0)),
": 2 = ",INDEX('Main Statistics'!B7:B55,MATCH(SMALL('Main Statistics'!S7:S55,1),'Main Statistics'!S7:S55,0)),",",INDEX('Main Statistics'!B7:B55,MATCH(SMALL('Main Statistics'!S7:S55,2),'Main Statistics'!S7:S55,0)),
": 3 = ",INDEX('Main Statistics'!B7:B55,MATCH(SMALL('Main Statistics'!S7:S55,1),'Main Statistics'!S7:S55,0)),",",INDEX('Main Statistics'!B7:B55,MATCH(SMALL('Main Statistics'!S7:S55,2),'Main Statistics'!S7:S55,0)),",",INDEX('Main Statistics'!B7:B55,MATCH(SMALL('Main Statistics'!S7:S55,3),'Main Statistics'!S7:S55,0)))

Thanks to those who looked.

 

[Aladin Akyurek]

I would like to Concatenate the two largest numbers in cells M7:M55 that reflect the numbers in cell B7:B55.

This achieves the correct result for the first number, but I can't seem to be able to get the concatenate to work.

=INDEX(B7:B55,MATCH(LARGE(M7:M55,1),M7:M55,0))

So basically, whatever the two largest numbers in M are, it returns the values in B.

I know I could use:-

=INDEX(B7:B55,MATCH(LARGE(M7:M55,1),M7:M55,0))&"   "&INDEX(B7:B55,MATCH(LARGE(M7:M55,2),M7:M55,0))

but I thought there might be an easier way.

Thanks in advance.

Control+shift+enter, not just enter:

=INDEX(B7:B55,MATCH(MAX(M7:M55),M7:M55,0))&" "&
  INDEX(B7:B55,MATCH(MAX(IF(1-(M7:M55=MAX(M7:M55)),M7:M55)),
   IF(1-(M7:M55=MAX(M7:M55)),M7:M55),0))

 

[S.H.A.D.O.]

Thanks Aladin for the reply, I appreciate it.

 

[Aladin Akyurek]

Thanks Aladin for the reply, I appreciate it.

You are welcome.