Finding the nth Occurance of an associate value.

[JeffGrant]

Hi All,

Thank you in advance for your assistance.

I have a range of numbers in column B and associated names in column A.

Using the formulas below, I return the 5 lowest numbers in the range. No problem. As long as there arent any duplicate numbers, the associated names are returned. All Ok there too.

However, when then are duplicate or triplicate numbers in column B then only the 1st occurance of the name is returned from column A.

I have tried various, Let, If, etc, type of formulas and do have someting that "kind of works". But I have noticed that there are occasions where the formulas dont work.

Using the number is column B, the names in Column A, I would like to return the names as in row 7.

I have struggled with this for a while, hoping somebody can assist.

Book1
	A	B	C	D	E	F	G	H
1				Occurance
2	Name	Number		1st	2nd	3rd	4th	5th
3	A	27		2	4	4	5	5
4	B	26		T	G	G	O	O
5	C	20
6	D	12		Should Be
7	E	23		T	G	Y	O	Z
8	F	7
9	G	4
10	H	101
11	I	14
12	J	13
13	K	8
14	L	11
15	M	10
16	N	46
17	O	5
18	P	9
19	Q	21
20	R	17
21	S	56
22	T	2
23	U	14
24	V	18
25	W	25
26	X	20
27	Y	4
28	Z	5
Sheet1

Cell Formulas
Range	Formula
D3	D3	=SMALL($B$3:$B$28,COUNTIF($B$3:$B$28,0)+1)
E3	E3	=SMALL($B$3:$B$28,COUNTIF($B$3:$B$28,0)+2)
F3	F3	=SMALL($B$3:$B$28,COUNTIF($B$3:$B$28,0)+3)
G3	G3	=SMALL($B$3:$B$28,COUNTIF($B$3:$B$28,0)+4)
H3	H3	=SMALL($B$3:$B$28,COUNTIF($B$3:$B$28,0)+5)
D4:H4	D4	=INDEX($A:$A,MMULT(SMALL(($B2:$B$28<>D$3)/1%%+ROW($2:$28),1),1))

 

[bebo021999]

D4:

=INDEX($A:$A,AGGREGATE(15,6,ROW($B$3:$B$28)/($B$3:$B$28=D3),COUNTIF($D$3:D3,D3)))

drag accross

 

[Fluff]

Another option for 365

Fluff.xlsm
	A	B	C	D	E	F	G	H
1				Occurance
2	Name	Number		1st	2nd	3rd	4th	5th
3	A	27		2	4	4	5	5
4	B	26		T	G	Y	O	Z
5	C	20
6	D	12
7	E	23
8	F	7
9	G	4
10	H	101
11	I	14
12	J	13
13	K	8
14	L	11
15	M	10
16	N	46
17	O	5
18	P	9
19	Q	21
20	R	17
21	S	56
22	T	2
23	U	14
24	V	18
25	W	25
26	X	20
27	Y	4
28	Z	5
Tracker

Cell Formulas
Range	Formula
D3:H3	D3	=INDEX(SORT(B3:B28),SEQUENCE(,5))
D4:H4	D4	=INDEX(SORT(A3:B28,2),SEQUENCE(,5),1)
Dynamic array formulas.

 

[Peter_SSs]

.. or this single formula in D3?

22 06 10.xlsm
	A	B	C	D	E	F	G	H
1				Occurance
2	Name	Number		1st	2nd	3rd	4th	5th
3	A	27		2	4	4	5	5
4	B	26		T	G	Y	O	Z
5	C	20
6	D	12
7	E	23
8	F	7
9	G	4
10	H	101
11	I	14
12	J	13
13	K	8
14	L	11
15	M	10
16	N	46
17	O	5
18	P	9
19	Q	21
20	R	17
21	S	56
22	T	2
23	U	14
24	V	18
25	W	25
26	X	20
27	Y	4
28	Z	5
List n Sorted

Cell Formulas
Range	Formula
D3:H4	D3	=TRANSPOSE(INDEX(SORT(A3:B28,2),SEQUENCE(5),{2,1}))
Dynamic array formulas.

 

[JeffGrant]

Hi All,

thank you so very much for your valuable contributions and solutions. I wish I could buy you all a beer or coffee for helping me out. In this case, I went with Peter's solution because when his formula was applied to the real data, it also knocked out several "helper" columns in one go.

I can not emphasis enough my gratitute to you all.

Jeff

 

[Peter_SSs]

You're welcome. Glad we could help. Thanks for the follow-up.