how to find the 3rd largest number in a multi dimensional lookup

[g2506]

Hi everyone,

I would like to know where I would need to include the Large(array,number) function in the formula below in order to get the 3rd largest number from column "am". Please tell me what is needed to complete this.(thanks)


=IF(COUNTIFS('Keyword report'!AM:AM,">0",'Keyword report'!AK:AK,G10:AD10),INDEX('Keyword report'!AJ:AO,MATCH(G10:AD10,'Keyword report'!AK5:AK1048576,0),MATCH('Keyword report'!AJ4,'Keyword report'!AJ4:AO4,0)),"--None--")

The array structure is as follows:
<table border="0" cellpadding="0" cellspacing="0" height="91" width="471"><colgroup><col style="mso-width-source:userset;mso-width-alt:8777;width:180pt" width="240"> <col style="mso-width-source:userset;mso-width-alt:7424;width:152pt" width="203"> <col style="width:48pt" span="4" width="64"> </colgroup><tbody><tr style="mso-height-source:userset;height:39.75pt" height="53"> <td style="height:39.75pt;width:180pt" height="53" width="240">aj</td> <td style="width:152pt" width="203">ak</td> <td style="width:48pt" width="64">al</td> <td style="width:48pt" width="64">am</td> <td style="width:48pt" width="64">an</td> <td style="width:48pt" width="64">ao</td> </tr> <tr style="height:15.0pt" height="20"> <td class="xl68" style="height:15.0pt" height="20">Keyword</td> <td class="xl68">Campaign</td> <td class="xl68">Clicks</td> <td class="xl67">Imp</td> <td class="xl68">Cost</td> <td class="xl68">Conv.</td></tr></tbody></table>

 

[Gerald Higgins]

Hi, welcome to the board.

If you're sure that you want to pull the 3rd largest number from Column AM, then this isn't actually a 3d array for that specific analysis.
You need something like this

=Large(am1:am10,3)

Bear in mind that the large function may not deal with duplicate values the way you want to.

If, on the other hand, you are not certain that the 3rd largest value is actually in Col AM, and it could be in other columns, then you do have some kind of 3d array.
There are probably ways of handling that - can you give more details about your situation please ?

 

[g2506]

Thank you for the reply.

What I really want to do is to get the 3rd keyword from column "aj". I want to get the 3rd keyword based on the 3rd largest number in column "am". I now that the "large" function should help me, but i'm not able to figure out where in the formula to apply it.

I tried to apply it around the "value" argument of the match function, and I also tried to apply it around the "array argument" of the match function.

This formula currently works on getting the keyword with the highest number from column "am". I want to try and get the 3rd largest number by using the "large" function.

Please let me know if you have additional questions, so that you can help me solve this.

thanks,
g

 

[Gerald Higgins]

Ah, so you want to find the keyword from col AJ that corresponds to the 3rd largest number in col AM ?

Here goes.

The formula I gave you earlier will tell you what the 3rd largest number is (bear in mind what I said about duplicate values).

What you need to do next is find the position of that value, in col AM, and the MATCH function will do this for you.

=MATCH(LARGE(AM1:AM10,3),AM1:AM10,0)

For example, if the 3rd largest value happens to be in AM6, then the above will return 6.

Finally, you need to find the 6th value in col AJ (or whatever value is returned by the match function)

=INDEX(AJ1:AJ10,MATCH(LARGE(AM1:AM10,3),AM1:AM10,0))