fetch 2nd largest value from specific columns

[sofroz]

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!

 

[shobolos]

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)),"")

 

[pgc01]

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.

 

[pgc01]

Or, not array entered:

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

 

[sofroz]

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,