Two separate arrays, need use LARGE to find largest number in second

[JoeJackson12]

Hi All,

I need to find the largest value in an array, based on the value of a year heading in another array at the top of the page. So I have this:

[TABLE="width: 220"]
<tbody>[TR]
[TD][/TD]
[TD="align: right"][B]2014[/B]
[/TD]
[TD="align: right"][B]2015[/B]
[/TD]
[TD="align: right"][B]2015[/B]
[/TD]
[TD="align: right"][B]2017[/B]
[/TD]
[/TR]
[TR]
[TD]Set A
[/TD]
[TD="align: right"]9
[/TD]
[TD="align: right"]35
[/TD]
[TD="align: right"]23
[/TD]
[TD="align: right"]48
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]48
[/TD]
[TD="align: right"]38
[/TD]
[TD="align: right"]14
[/TD]
[TD="align: right"]22
[/TD]
[/TR]
[TR]
[TD]Set B
[/TD]
[TD="align: right"]15
[/TD]
[TD="align: right"]34
[/TD]
[TD="align: right"]17
[/TD]
[TD="align: right"]8
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]22
[/TD]
[TD="align: right"]25
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]21
[/TD]
[/TR]
[TR]
[TD]Set C
[/TD]
[TD="align: right"]1
[/TD]
[TD="align: right"]47
[/TD]
[TD="align: right"]40
[/TD]
[TD="align: right"]2
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]39
[/TD]
[TD="align: right"]19
[/TD]
[TD="align: right"]18
[/TD]
[TD="align: right"][/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]29
[/TD]
[TD="align: right"]6
[/TD]
[TD="align: right"]49
[/TD]
[TD="align: right"]48
[/TD]
[/TR]
[TR]
[TD][/TD]
[TD="align: right"]33
[/TD]
[TD="align: right"]42
[/TD]
[TD="align: right"]50
[/TD]
[TD="align: right"]10
[/TD]
[/TR]
</tbody>[/TABLE]

I need a function that I can plug in 2017 as the year to look up in the header A1:A4, and look for second-largest value in the array for Set C (currently K16:N19), and it will return 10. I need to do this function multiple times, so can't use an array function, but can hardcode the array K16:N19.

So far I'm using something like this

=MAX(INDEX(array1,MATCH(2017,array2,0),0))

Any help is much appreciated...thank you!

 

[Tetra201]

Try this:

=LARGE(INDEX($K$16:$N$19,0,MATCH(2017,$K$11:$N$11,0)),2)

 

[JoeJackson12]

Worked like a charm ... Thanks so much!

 

[JoeJackson12]

Okay @Tetra201 and All, I have another question...now, I want to take that same second-largest value, and return the accompanying customer name in Column M...any ideas as to how to do this?

Thanks again!

 

[Tetra201]

...now, I want to take that same second-largest value, and return the accompanying customer name in Column M...

The corresponding cell in column M has a numerical value of 50, does it not?

 

[JoeJackson12]

Excuse me, you're right, I shifted my table... corresponding Column P is what I should have written...that's the column from which I'm trying to pull the data.

 

[Tetra201]

Try

=INDEX($P$16:$P$19,MATCH(LARGE(INDEX($K$16:$N$19,0,MATCH(2017,$K$11:$N$11,0)),2),INDEX($K$16:$N$19,0,MATCH(2017,$K$11:$N$11,0)),0))

 

[JoeJackson12]

Wow...also works like a charm! Thank you!!!!!