# Concatenate, INDEX, MATCH, LARGE.

#### S.H.A.D.O.

##### Well-known Member
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.

Code:
``=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:-

Code:
``=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.

Last edited:

### Excel Facts

How to find 2nd largest value in a column?
MAX finds the largest value. =LARGE(A:A,2) will find the second largest. =SMALL(A:A,3) will find the third smallest
OK, I have adapted the formula and got it working:-

Code:
``````=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.

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.

Code:
``=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:-

Code:
``````=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.

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.

Code:
``=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:-

Code:
``=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.

Control+shift+enter, not just enter:
Rich (BB code):
``````=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))``````

Replies
1
Views
173
Replies
7
Views
796
Replies
0
Views
342
Replies
2
Views
379
Replies
3
Views
371

### Forum statistics

1,203,384
Messages
6,055,118
Members
444,763
Latest member
Jaapaap ### 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.

### Which adblocker are you using?    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

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