INDIRECT combined with something else

[Lucky707]

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?

 

[Lucky707]

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]

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]

Did you want something like this?

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	964	200	194	470	91	680	157	524		A	B	C	D	E	F
2	555	523	29	251	299	709	974	527		3	985	754	608	505	412
3	412	230	82	283	985	505	608	754
4	723	211	712	955	346	150	96	228
5	967	413	93	466	2	16	975	767
6	457	119	305	708	554	285	869	737
7	369	862	957	550	444	119	297	687
8	164	286	761	440	397	376	332	870
9	658	945	867	475	460	789	391	171
Sheet1

In K2 and pasted over:

=LARGE(INDEX($A$1:$A$100,$J2):INDEX($H$1:$H$100,$J2),COLUMN()-COLUMN($J2))

 

[Lucky707]

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

 

[Lucky707]

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]

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
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	964	200	194	470	91	680	157	524		A	B	C	D	E	F
2	555	523	29	251	299	709	974	527		3	985	754	608	505	412
3	412	230	82	283	985	505	608	754			E	H	G	F	A
4	723	211	712	955	346	150	96	228
5	967	413	93	466	2	16	975	767
6	457	119	305	708	554	285	869	737
7	369	862	957	550	444	119	297	687
8	164	286	761	440	397	376	332	870
9	658	945	867	475	460	789	391	171
Lucky707

 

[PA HS Teacher]

How about:

Book1
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O
1	964	200	194	470	91	680	157	524		A	B	C	D	E	F
2	555	523	29	251	299	709	974	527		3	985	754	608	505	412
3	412	230	82	283	985	505	608	754			E	H	G	F	A
4	723	211	712	955	346	150	96	228
5	967	413	93	466	2	16	975	767
6	457	119	305	708	554	285	869	737
7	369	862	957	550	444	119	297	687
8	164	286	761	440	397	376	332	870
9	658	945	867	475	460	789	391	171
Sheet1

Same as my original post, plus in K3 and pasted over:

=CHAR(MATCH(K2,INDEX($A$1:$A$100,$J2):INDEX($H$1:$H$100,$J2),0)+64)