# Nesting the large formula inside a cell formula

#### pbarvind

##### New Member
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

Workdays for a market open Mon, Wed, Friday?
Yes! Use "0101011" for the weekend argument in NETWORKDAYS.INTL or WORKDAY.INTL. The 7 digits start on Monday. 1 means it is a weekend.

#### Ron Coderre

##### MrExcel MVP
With
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
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
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
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!

Replies
4
Views
110
Replies
6
Views
305
Replies
5
Views
247
Replies
4
Views
253
Replies
2
Views
228

1,171,203
Messages
5,874,330
Members
433,044
Latest member
drewbizzy

### 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.

### Which adblocker are you using?

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

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