Nesting the large formula inside a cell formula

pbarvind

New Member
Joined
Aug 18, 2011
Messages
28
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!
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)

Ron Coderre

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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
Code:
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:
Code:
=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?
 
Last edited:

Yahya

Well-known Member
Joined
Mar 28, 2009
Messages
648
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

MrExcel MVP
Joined
Jan 14, 2009
Messages
2,316
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

New Member
Joined
Aug 18, 2011
Messages
28
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!
 

Forum statistics

Threads
1,136,732
Messages
5,677,432
Members
419,692
Latest member
Also_Confused

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.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

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

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

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
Top