# fetch 2nd largest value from specific columns

#### sofroz

##### New Member
Hi all,

I'm a little stuck with a formula.

Sample data is below. Basically what I'm hoping to get is the 2nd largest value from columns containing "on-going" in their title. I can't use an INDEX/MATCH formula because sometimes, 'initial' and 'ongoing' rows have the same values.

 A B C D E F 1 Arts & crafts Lifestyle Sports Collection Observation 2 Ad (initial) 54 45 89 67 88 3 Ad (ongoing) 32 67 54 55 38 4 Newsletter (initial) 34 90 50 99 93 5 Newsletter (ongoing) 12 100 49 320 150 6 Flyer (initial) 3 146 11 349 583 7 Flyer (ongoing) 54 31 7 400 230

<tbody>
</tbody>

Any help would be appreciated!

### 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
In cell G2 paste and copy down the formula =IFERROR(IF(FIND("ongoing",A2),AGGREGATE(14,6,B2:F2,2),""),"")

In cell H2 paste and copy down the formula =IFERROR(INDEX(\$B\$1:\$F\$1,,MATCH(G2,B2:F2,0)),"")

Hi
Try:

=LARGE(IF(ISNUMBER(SEARCH("ongoing",A2:A7)),B2:F7),2)

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Or, not array entered:

=AGGREGATE(14;6;B2:F7/(SEARCH("ongoing";A2:A7)>0);2)

Hi
Try:

=LARGE(IF(ISNUMBER(SEARCH("ongoing",A2:A7)),B2:F7),2)

This is an array formula, you have to confirm it with CTRL-SHIFT-ENTER and not just ENTER.

Thank you for getting back to me so quickly on this.

I forgot to mention that I was hoping to get the 2nd largest value for each column (e.g arts & crafts, lifestyle etc..) and I want it to return the heading not the largest value.

Changed your formula to the one below but I was hoping to modify it so I don't have change the last part for each column...

=INDEX(\$B\$17:\$B\$22,MATCH(LARGE(IF(ISNUMBER(SEARCH("ongoing",\$B\$17:\$B\$22)),C\$17:C\$22),2),C\$17:C\$22,0))

Thanks again,

Replies
3
Views
559
Replies
1
Views
236
Replies
2
Views
200
Replies
3
Views
212
Replies
0
Views
236

1,203,109
Messages
6,053,563
Members
444,673
Latest member

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