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?
 

Some videos you may like

Excel Facts

What is the last column in Excel?
Excel columns run from A to Z, AA to AZ, AAA to XFD. The last column is XFD.

Lucky707

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

babycody

Well-known Member
Joined
Jul 8, 2003
Messages
1,395
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.
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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))
 

Lucky707

New Member
Joined
Aug 1, 2005
Messages
11

ADVERTISEMENT

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

Lucky707

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

Peter_SSs

MrExcel MVP, Moderator
Joined
May 28, 2005
Messages
47,926
Office Version
  1. 365
Platform
  1. Windows
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
 

PA HS Teacher

Well-known Member
Joined
Jul 17, 2004
Messages
2,838
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)
 

Watch MrExcel Video

Forum statistics

Threads
1,123,203
Messages
5,600,291
Members
414,375
Latest member
Onmyown

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