Duplicate values using xlookup

Question

I am getting the following response when trying to look for the Subcomp (Subcompetency) associated with the lowest Subcomp score. Xlookup keeps finding the first occurrence when there are multiples with same value for Subcomp score. I know there are other posts about this but wondering if someone could help me apply those formulas with my data? I am using this formula to get Subcomp score =SMALL('Download sheet'!$N$4:$N$101,1) and this formula for Subcomp=XLOOKUP('Download sheet'!$S$37,('Download sheet'!$N$4:$N$101),'Download sheet'!$M$4:$M$101)

student	subcomp score	Subcomp	Rotation
George Jetson	2.00	3.1	2
George Jetson	2.03	5.1	2
Fred Flinstone	2.07	2.1.9	1
Fred Flinstone	2.07	2.1.9	1
Wilma Flintstone	2.09	7.1.2	2
Wilma Flintstone	2.11	8.2	2
George Jetson	2.12	1.7	1
George Jetson	2.12	1.7	1
George Jetson	2.15	4.1	1
George Jetson	2.15	4.1	1

Here is source data:

First and Last Name	Rotation	Domain	SubComp	SubComp Score
George Jetson	1	1	1.1	2.89
George Jetson	1	1	1.2	3.09
Fred Flinstone	1	1	1.3	4.02
George Jetson	1	1	1.4	4.78
George Jetson	1	1	1.5	3.62
Whilma Flinstone	1	1	1.6	4.25
George Jetson	1	1	1.7	2.12
George Jetson	1	2	2.1	3.42
Fred Flinstone	1	2	2.1.1	4.35
George Jetson	1	2	2.1.2	2.36
George Jetson	1	2	2.1.3	4.37
George Jetson	1	2	2.1.4	3.08
George Jetson	1	2	2.1.5	3.75
George Jetson	1	2	2.1.6	3.53
Fred Flinstone	1	2	2.1.7	2.25
George Jetson	1	2	2.1.8	2.12
Fred Flinstone	1	2	2.1.9	2.07
George Jetson	1	2	2.2	4.15
George Jetson	1	2	2.2.1	4.89
George Jetson	1	3	3.1	4.02
George Jetson	1	3	3.1.1	4.77
George Jetson	1	3	3.1.2	4.95
George Jetson	1	3	3.2	4.45
George Jetson	1	3	3.3	4.91
George Jetson	1	4	4.1	2.15
George Jetson	1	4	4.2	2.29
George Jetson	1	5	5.1	2.48
George Jetson	1	5	5.2	4.37
George Jetson	1	5	5.3	4.01
whilma Flinstone	1	6	6.1	2.15
George Jetson	1	6	6.2	4.26
George Jetson	1	6	6.3	3.50
Fred Flinstone	1	6	6.4	3.65
George Jetson	1	6	6.5	4.83
George Jetson	1	7	7.1	3.82
George Jetson	1	7	7.1.1	3.33
George Jetson	1	7	7.1.2	2.82
George Jetson	1	7	7.1.3	5.00
George Jetson	1	7	7.1.4	4.67
Fred Flinstone	1	7	7.2	4.21
George Jetson	1	7	7.3	3.70
George Jetson	1	7	7.4	4.09
George Jetson	1	7	7.5	4.53
George Jetson	1	8	8.1	3.53
George Jetson	1	8	8.2	2.50
George Jetson	1	8	8.3	4.09
George Jetson	1	9	9.1	3.40
George Jetson	1	9	9.2	3.00
George Jetson	1	9	9.3	3.88
George Jetson	2	1	1.1	2.86
George Jetson	2	1	1.2	3.23
Fred Flinstone	2	1	1.3	3.53
George Jetson	2	1	1.4	2.84
George Jetson	2	1	1.5	2.28
George Jetson	2	1	1.6	5.00
George Jetson	2	1	1.7	2.65
George Jetson	2	2	2.1	3.10
Fred Flinstone	2	2	2.1.1	3.08
George Jetson	2	2	2.1.2	2.07
George Jetson	2	2	2.1.3	4.54
George Jetson	2	2	2.1.4	2.30
George Jetson	2	2	2.1.5	3.46
George Jetson	2	2	2.1.6	2.30
Fred Flinstone	2	2	2.1.7	4.34
George Jetson	2	2	2.1.8	3.62
Fred Flinstone	2	2	2.1.9	4.32
George Jetson	2	2	2.2	3.65
George Jetson	2	2	2.2.1	2.44
George Jetson	2	3	3.1	2.00
George Jetson	2	3	3.1.1	4.92
George Jetson	2	3	3.1.2	3.68
George Jetson	2	3	3.2	2.97
George Jetson	2	3	3.3	4.74
George Jetson	2	4	4.1	3.49
George Jetson	2	4	4.2	3.14
George Jetson	2	5	5.1	2.03
George Jetson	2	5	5.2	3.33
George Jetson	2	5	5.3	2.57
George Jetson	2	6	6.1	4.17
George Jetson	2	6	6.2	2.22
George Jetson	2	6	6.3	3.60
Wilma Flintstone	2	6	6.4	2.38
Wilma Flintstone	2	6	6.5	3.98
Wilma Flintstone	2	7	7.1	2.40
Wilma Flintstone	2	7	7.1.1	3.57
Wilma Flintstone	2	7	7.1.2	2.09
Wilma Flintstone	2	7	7.1.3	2.53
Fred Flinstone	2	7	7.1.4	2.67
Wilma Flintstone	2	7	7.2	2.39
Wilma Flintstone	2	7	7.3	3.08
Wilma Flintstone	2	7	7.4	3.03
Wilma Flintstone	2	7	7.5	4.17
Wilma Flintstone	2	8	8.1	3.99
Wilma Flintstone	2	8	8.2	2.11
Fred Flinstone	2	8	8.3	3.12
Wilma Flintstone	2	9	9.1	2.46
Wilma Flintstone	2	9	9.2	4.71
Wilma Flintstone	2	9	9.3	2.67

detlef_lewin · Answer

Try this:=SORT(CHOOSECOLS('Download sheet'!$A$4:$E$101,1,5,4,2),2,1)&nbsp;

john_d2255 · Answer

Will try. Thanks! I am also trying a filter function embedded in a sort function

Forum Discussion

Duplicate values using xlookup

2 Replies

Resources