# INDIRECT combined with something else

#### Lucky707

##### New Member
I have a set of say... 8 random numbers.

Let's say I were to fill down, giving me say... 100 sets of 8 random numbers from row 1 to row 100.

Let's say I have five boxes.

[A]

[C]
[D]
[E]

In box A, I will type a number from 1 to 100. Call it n.

In box B, I want the largest number in row n.
In box C, I want the second largest number in row n.
In box D, I want the third largest number in row n.
In Box E, I want the fourth largest number in row n.

How to do that?

### Excel Facts

Copy a format multiple times
Select a formatted range. Double-click the Format Painter (left side of Home tab). You can paste formatting multiple times. Esc to stop
Should be mentioned that there is data beside the 8 random numbers that are also numbers that I DON'T want included in any way.

Try the LARGE function i.e. =LARGE(\$A\$1:\$A\$100,2) where the 2 would be the nth largest number. In the example it would be the second largest number.

Did you want something like this?
Book1
ABCDEFGHIJKLMNO
196420019447091680157524ABCDEF
2555523292512997099745273985754608505412
341223082283985505608754
472321171295534615096228
596741393466216975767
6457119305708554285869737
7369862957550444119297687
8164286761440397376332870
9658945867475460789391171
Sheet1

In K2 and pasted over:
Code:
``=LARGE(INDEX(\$A\$1:\$A\$100,\$J2):INDEX(\$H\$1:\$H\$100,\$J2),COLUMN()-COLUMN(\$J2))``

Yes, that's more or less what I wanted. Thank you.

Follow-Up:
In your example, let's say in row 3 i wanted, underneath the number I wanted it to list the COLUMN it got the number from. How would I do that?

eg. in your example, K3 = E, L3 = H, and so on

Lucky707

Here is an alternative to the first question, and one solution to the last one.

Formula in K2 (copied across):
=LARGE(OFFSET(\$A\$1,\$J2-1,0,1,8),CODE(K1)-65)

Formula in K3 (copied across):
=CHAR(64+MATCH(K2,OFFSET(\$A\$1,\$J2-1,0,1,8),0))

Both of these formulas contain the digit 8 because the original data is 8 columns wide. If this width needs to be flexible, I am sure the 8 could be replaced with some more flexible (but longer) code.
Mr Excel.xls
ABCDEFGHIJKLMNO
196420019447091680157524ABCDEF
2555523292512997099745273985754608505412
341223082283985505608754EHGFA
472321171295534615096228
596741393466216975767
6457119305708554285869737
7369862957550444119297687
8164286761440397376332870
9658945867475460789391171
Lucky707

Book1
ABCDEFGHIJKLMNO
196420019447091680157524ABCDEF
2555523292512997099745273985754608505412
341223082283985505608754EHGFA
472321171295534615096228
596741393466216975767
6457119305708554285869737
7369862957550444119297687
8164286761440397376332870
9658945867475460789391171
Sheet1

Same as my original post, plus in K3 and pasted over:
Code:
``=CHAR(MATCH(K2,INDEX(\$A\$1:\$A\$100,\$J2):INDEX(\$H\$1:\$H\$100,\$J2),0)+64)``

Replies
1
Views
97
Replies
1
Views
316
Replies
1
Views
140
Replies
4
Views
525
Replies
5
Views
251

1,203,241
Messages
6,054,321
Members
444,717
Latest member
melindanegron

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