help with lookup

[Noxqss38242]

Have values in Column U
Have names in Column K

example:
K U
Banks 20
Beard 24
Collie 26
Foster 26
Martin 34
Parish 34
Pinson 37

These ranks will change depending on what options are chosen on a slicer. Column U is a formula to pull small/large based on values added from columns N, O, P.

Long story short, how do I extract names based of U if there are duplicate values? How do I get it to pull Collie and Foster?
Currently it is pulling Collie and Collie as it stops looking as soon as a match is found.

Hope that makes sense...

 

[Eric W]

Maybe:

Book1 (version 1).xlsb
	K										U	V	W	X
1
2	Banks										20		amt	Names
3	Beard										24		26	Collie
4	Collie										26			Foster
5	Foster										26
6	Martin										34
7	Parish										34
8	Pinson										37
9	Pinson										37
Sheet4

Cell Formulas
Range	Formula
X3:X6	X3	=IFERROR(INDEX(K:K,AGGREGATE(15,6,ROW($K$2:$K$9)/($U$2:$U$9=$W$3),ROWS($X$3:$X3))),"")

 

[Noxqss38242]

Productivity Report.testing.xlsx
	N	O	P	Q	R	S	T	U	V
65	10	28	26	64			20
66	22	4	28	54			24
67	21	13	12	46			26
68	9	8	11	28			26
69	6	11	3	20			28
70	30	18	19	67			34
71				0			34
72				0
73	3	22	1	26
74	19	23	32	74
75	26	12	17	55
76	8	31	5	44
77	32	27	22	81
78	18	10	10	38
79	20	21	16	57
80	14	6	27	47
81	7	29	4	40
82	12	5	7	24
83	25	30	33	88
84	4	20	2	26
85	11	17	6	34
86	27	16	21	64
87	23	24	15	62
88	33	3	24	60
89	13	19	14	46
90	28	25	18	71
91	31	33	29	93
92	17	7	13	37
93	2	14	30	46
94	5	15	20	40
95	1	2	31	34
96	15	32	8	55
97	16	9	9	34
98				0
99	24	26	25	75
100	29	1	23	53
Report Card

Cell Formulas
Range	Formula
N65:N100	N65	=IFERROR(VLOOKUP(K65,$B$65:$C$100,2,0),"")
O65:O100	O65	=IFERROR(VLOOKUP(K65,$B$105:$C$140,2,0),"")
P65:P100	P65	=IFERROR(VLOOKUP(K65,$B$145:$C$180,2,0),"")
Q65:Q100	Q65	=SUM(N65:P65)
T65	T65	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+1)
T66	T66	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+2)
T67	T67	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+3)
T68	T68	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+4)
T69	T69	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+5)
T70	T70	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+6)
T71	T71	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+7)

Names are in column K65:K100 (left out for privacy)

Column T are the "numbers" I'm wanting to match against the names
Same issue, a lookup is only pulling first instance and won't move past if name is already used, so it will put the name twice.

Hope this paints a better picture. Thank you for the help!

 

[Noxqss38242]

Previously my numbers were in column U but while trying the formula I thought leaving a gap between numbers may have caused the error so I moved the "sum" column left causing my "U" column to now be in column "T".

Wasn't trying to give you false info before, but I have tried your formula and it wasn't working so I was playing with variations to include removing the gap.
Now it is just the way it is posted...I still can't figure it out though. Playing Thomas Edison over here but still no luck...haha.

 

[Eric W]

Now I'm confused. Could you post a sample sheet, with the names, data, matching numbers, and location where you want the result? For the names, just something like a, b, c, etc. would be fine.

 

[Noxqss38242]

Productivity Report.testing.xlsx
	L	M	N	O	P	Q	R	S	T	U
64			TT	SRT	DT	SUM			Rank	Name
65	A		10	28	26	64			20	????
66	B		22	4	28	54			24	????
67	C		21	13	12	46			26	????
68	D		9	8	11	28			26	????
69	E		6	11	3	20			28	????
70	F		30	18	19	67			34	????
71	G					0			34	????
72	H					0
73	I		3	22	1	26
74	J		19	23	32	74
75	K		26	12	17	55
76	L		8	31	5	44
77	M		32	27	22	81
78	N		18	10	10	38
79	O		20	21	16	57
80	P		14	6	27	47
81	Q		7	29	4	40
82	R		12	5	7	24
83	S		25	30	33	88
84	T		4	20	2	26
85	U		11	17	6	34
86	V		27	16	21	64
87	W		23	24	15	62
88	X		33	3	24	60
89	Y		13	19	14	46
90	Z		28	25	18	71
91	AA		31	33	29	93
92	BB		17	7	13	37
93	CC		2	14	30	46
94	DD		5	15	20	40
95	EE		1	2	31	34
96	FF		15	32	8	55
97	GG		16	9	9	34
98	HH					0
99	II		24	26	25	75
100	JJ		29	1	23	53
Report Card

Cell Formulas
Range	Formula
N65:N100	N65	=IFERROR(VLOOKUP(K65,$B$65:$C$100,2,0),"")
O65:O100	O65	=IFERROR(VLOOKUP(K65,$B$105:$C$140,2,0),"")
P65:P100	P65	=IFERROR(VLOOKUP(K65,$B$145:$C$180,2,0),"")
Q65:Q100	Q65	=SUM(N65:P65)
T65	T65	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+1)
T66	T66	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+2)
T67	T67	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+3)
T68	T68	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+4)
T69	T69	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+5)
T70	T70	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+6)
T71	T71	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+7)

So in this instance L65:L100 has the names.
Column "U" with the ???? is where I want the answers/formula.

Rank 20 should = E
Rank 24 =R
Rank 26 = I
Rank 26 = T
Rank 28 = D
Rank 34 = U
Rank 34 = EE

the way I have the "rank" pulling numbers is to ignore the zeros so I then have the lowest scores overall.
Problem I'm having is that my lookup is pulling the first name only so for "rank 26" it is pulling I twice and for "rank 34" it is pulling "U" twice.

 

[Eric W]

With the way your sheet is set up, try this:

Book1 (version 1).xlsb
	T	U
64	Rank	Name
65	20	E
66	24	R
67	26	I
68	26	T
69	28	D
70	34	U
71	34	EE
72
Sheet4

Cell Formulas
Range	Formula
T65	T65	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+1)
U65:U71	U65	=INDEX(L:L,AGGREGATE(15,6,ROW($Q$65:$Q$100)/($Q$65:$Q$100=T65),COUNTIF($T$65:$T65,T65)))
T66	T66	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+2)
T67	T67	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+3)
T68	T68	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+4)
T69	T69	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+5)
T70	T70	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+6)
T71	T71	=SMALL($Q$65:$Q$100,COUNTIF($Q$65:$Q$100,0)+7)

 

[Noxqss38242]

Perfect!

 

[Eric W]

Glad it works for you! Thanks for the update.