Nesting the large formula inside a cell formula

[pbarvind]

Hey,

Needed some help with nesting some formula. Suppose I have a row like this:

<pre>

A B C D E F
1 apple mango orange guava banana
2 2 4 1 5 5

</pre>

a) Now, if I want to return the 2nd largest value, i use =large(B1:F1,2)

b) If I need to return the cell reference of the second largest value, I try =cell(address,large(B1:F1,2))

but this doesn't seem to work. It returns an error. How can I write a formula to return the address of the 2nd largest value in the row?

Ideally, in this instance, I'd like to return "Mango", so I plan to nest a working version of b) within the offset formula.

Thank you!

 

[Ron Coderre]

With
Your posted data in A1:E2
and
H1: the nTh largest value to find....2

This regular formula returns the first Row_1 value that corresponds to that
nTh largest value

H2: =INDEX(A1:E1,MATCH(LARGE(INDEX((COLUMN($A$2:$E$2)=MATCH
($A$2:$E$2,$A$2:$E$2,0))*$A$2:$E$2,0),H1),A2:E2,0))

In your example, the formula returns: mango

EDITED TO INCLUDE THIS COMMENT
If you really want the cell reference...try this variation of that regular formula:

=CELL("address",INDEX(A1:E1,MATCH(LARGE(INDEX((COLUMN($A$2:$E$2)=MATCH
($A$2:$E$2,$A$2:$E$2,0))*$A$2:$E$2,0),H1),A2:E2,0)))

That formula would return: $B$1

Is that something you can work with?

 

[Yahya]

May be this
<b>Excel 2007</b><table cellpadding="2.5px" rules="all" style=";background-color: #FFFFFF;border: 1px solid;border-collapse: collapse; border-color: #A6AAB6"><colgroup><col width="25px" style="background-color: #E0E0F0" /><col /><col /><col /><col /><col /><col /></colgroup><thead><tr style=" background-color: #E0E0F0;text-align: center;color: #161120"><th></th><th>A</th><th>B</th><th>C</th><th>D</th><th>E</th><th>F</th></tr></thead><tbody><tr ><td style="color: #161120;text-align: center;">1</td><td style="text-align: right;;">1</td><td style=";">apple</td><td style=";">mango</td><td style=";">orange</td><td style=";">guava</td><td style=";">banana</td></tr><tr ><td style="color: #161120;text-align: center;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">2</td><td style="text-align: right;;">4</td><td style="text-align: right;;">1</td><td style="text-align: right;;">5</td><td style="text-align: right;;">5</td></tr><tr ><td style="color: #161120;text-align: center;">3</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">4</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">5</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">6</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr><tr ><td style="color: #161120;text-align: center;">7</td><td style=";">$C$1</td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td><td style="text-align: right;;"></td></tr></tbody></table><p style="width:4.2em;font-weight:bold;margin:0;padding:0.2em 0.6em 0.2em 0.5em;border: 1px solid #A6AAB6;border-top:none;text-align: center;background-color: #E0E0F0;color: #161120">Sheet12</p><br /><br /><table width="85%" cellpadding="2.5px" rules="all" style=";border: 2px solid black;border-collapse:collapse;padding: 0.4em;background-color: #FFFFFF" ><tr><td style="padding:6px" ><b>Array Formulas</b><table cellpadding="2.5px" width="100%" rules="all" style="border: 1px solid;text-align:center;background-color: #FFFFFF;border-collapse: collapse; border-color: #A6AAB6"><thead><tr style=" background-color: #E0E0F0;color: #161120"><th width="10px">Cell</th><th style="text-align:left;padding-left:5px;">Formula</th></tr></thead><tbody><tr><th width="10px" style=" background-color: #E0E0F0;color: #161120">A7</th><td style="text-align:left">{=ADDRESS(<font color="Blue">1,MATCH(<font color="Red">INDEX(<font color="Green">B1:F1,SMALL(<font color="Purple">IF(<font color="Teal">FREQUENCY(<font color="#FF00FF">IF(<font color="Navy">B2:F2<>"",MATCH(<font color="Blue">B2:F2,B2:F2,0</font>)</font>),COLUMN(<font color="Navy">B2:F2</font>)-COLUMN(<font color="Navy">B2</font>)+1</font>),TRANSPOSE(<font color="#FF00FF">B2:F2</font>)</font>),2</font>)</font>),A1:F1,0</font>)</font>)}</td></tr></tbody></table><b>Entered with Ctrl+Shift+Enter.</b> If entered correctly, Excel will surround with curly braces {}.
<b>Note: Do not try and enter the {} manually yourself</b></td></tr></table><br />

 

[Ron Coderre]

One clarification...I assumed your first item (apple) was in cell A1
and the first value (associated with apple) was in A2.

If that's not true, the references would need to be adjusted to include Col_F.

 

[pbarvind]

Ron and Yahya,

Thanks so much for your help! Given my data set, they needed some tweaking but it worked.

I had earlier achieved in a roundabout manner by having match return the column number and using another table with fruit names and column numbers to perform a vlookup.

Thanks again!