INDIRECT combined with something else

Lucky707

New Member
Joined
Aug 1, 2005
Messages
11
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

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.
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.
 
Upvote 0
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.
 
Upvote 0
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))
 
Upvote 0
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
 
Upvote 0
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
 
Upvote 0
How about:
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)
 
Upvote 0

Forum statistics

Threads
1,213,506
Messages
6,114,024
Members
448,543
Latest member
MartinLarkin

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
Back
Top