xlookup error proofing & cascade searching

[Afro_Cookie]

Can xlookup perform a cascade search in the event multiple rows show the same data?

I have some data I'm collating and analyzing to see which value is the largest and showing it at the top of the list. However, when there are two values that are the same, it only pulls the first instance, showing a duplicate of the data. I would like it to look at the first column, then if the numbers are the same, look at the second, and cascade down until it finds the unique value and uses that as the search reference for Xlookup.

Xl2BB isn't working right now due to new security, but I am posting the data and the formula used to hopefully help understand my file.

Columns A:D are raw data
Columns F:I are using a count if statement to see how many values are within my specified range for each category (Score 1: <0.300, Score 2: 0.300<0.500, Score 3: >0.500)
Column O is using the =Large() function to find the largest value
Columns L:N use Xlookup based on O:O to return the other values.
Column L is what I want to be unique after Columns L:O are populated.

As you can see in Column L, there are two instances of 'C' where one should be 'F'

A

B

C

D

E

F

G

H

I

J

K

L

M

N

O

Subject

Score 1

Score 2

Score 3

Subject

Score 1

Score 2

Score 3

Subject

Score 1

Score 2

Score 3

a

0.609​

0.841​

0.629​

a

16​

10​

17​

1​

e

10​

3​

30​

b

0.425​

0.278​

0.496​

b

16​

8​

18​

2​

d

12​

7​

24​

c

0.855​

0.770​

0.385​

c

9​

11​

23​

3​

c

9​

11​

23​

d

0.849​

0.798​

0.002​

d

12​

7​

24​

4​

c

9​

11​

23​

e

0.505​

0.406​

0.750​

e

10​

3​

30​

5​

b

16​

8​

18​

f

0.332​

0.365​

0.055​

f

11​

9​

23​

6​

a

16​

10​

17​

a

0.591​

0.775​

0.462​

b

0.049​

0.415​

0.629​

c

0.294​

0.661​

0.771​

d

0.443​

0.504​

0.828​

e

0.611​

0.322​

0.394​

f

0.899​

0.687​

0.302​

a

0.226​

0.919​

0.649​

b

0.269​

0.220​

0.755​

c

0.606​

0.824​

0.515​

d

0.032​

0.250​

0.506​

e

0.263​

0.538​

0.176​

f

0.290​

0.899​

0.947​

a

0.151​

0.812​

0.398​

b

0.978​

0.973​

0.114​

c

0.441​

0.469​

0.537​

d

0.734​

0.000​

0.367​

e

0.630​

0.942​

0.819​

f

0.593​

0.167​

0.971​

a

0.852​

0.680​

0.414​

b

0.225​

0.242​

0.482​

c

0.717​

0.896​

0.414​

d

0.644​

0.449​

0.068​

e

0.963​

0.684​

0.803​

f

0.751​

0.387​

0.243​

a

0.059​

0.861​

0.581​

b

0.407​

0.164​

0.374​

c

0.365​

0.305​

0.449​

d

0.757​

0.965​

0.942​

e

0.981​

0.833​

0.218​

f

0.262​

0.402​

0.983​

a

0.163​

0.633​

0.894​

b

0.035​

0.613​

0.327​

c

0.410​

0.528​

0.289​

d

0.110​

0.625​

0.945​

e

0.844​

0.040​

0.556​

f

0.653​

0.380​

0.985​

a

0.013​

0.661​

0.720​

b

0.763​

0.286​

0.853​

c

0.025​

0.009​

0.328​

d

0.928​

0.083​

0.105​

e

0.057​

0.425​

0.196​

f

0.597​

0.726​

0.281​

a

0.348​

0.379​

0.847​

b

0.293​

0.200​

0.517​

c

0.940​

0.799​

0.212​

d

0.518​

0.833​

0.943​

e

0.614​

0.430​

0.726​

f

0.148​

0.015​

0.906​

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D							K	L	M	N	O
1	Subject	Score 1	Score 2	Score 3								Subject	Score 1	Score 2	Score 3
2	a	0.609	0.841	0.629							1	a	5	1	6
3	b	0.425	0.278	0.496							2	d	2	1	5
4	c	0.855	0.77	0.385							3	e	2	4	5
5	d	0.849	0.798	0.002							4	f	3	4	5
6	e	0.505	0.406	0.75							5	b	5	1	4
7	f	0.332	0.365	0.055							6	c	2	2	3
8	a	0.591	0.775	0.462
9	b	0.049	0.415	0.629
10	c	0.294	0.661	0.771
11	d	0.443	0.504	0.828
12	e	0.611	0.322	0.394
13	f	0.899	0.687	0.302
14	a	0.226	0.919	0.649
15	b	0.269	0.22	0.755
16	c	0.606	0.824	0.515
17	d	0.032	0.25	0.506
18	e	0.263	0.538	0.176
19	f	0.29	0.899	0.947
20	a	0.151	0.812	0.398
21	b	0.978	0.973	0.114
22	c	0.441	0.469	0.537
23	d	0.734	0	0.367
24	e	0.63	0.942	0.819
25	f	0.593	0.167	0.971
26	a	0.852	0.68	0.414
27	b	0.225	0.242	0.482
28	c	0.717	0.896	0.414
29	d	0.644	0.449	0.068
30	e	0.963	0.684	0.803
31	f	0.751	0.387	0.243
32	a	0.059	0.861	0.581
33	b	0.407	0.164	0.374
34	c	0.365	0.305	0.449
35	d	0.757	0.965	0.942
36	e	0.981	0.833	0.218
37	f	0.262	0.402	0.983
38	a	0.163	0.633	0.894
39	b	0.035	0.613	0.327
40	c	0.41	0.528	0.289
41	d	0.11	0.625	0.945
42	e	0.844	0.04	0.556
43	f	0.653	0.38	0.985
44	a	0.013	0.661	0.72
45	b	0.763	0.286	0.853
46	c	0.025	0.009	0.328
47	d	0.928	0.083	0.105
48	e	0.057	0.425	0.196
49	f	0.597	0.726	0.281
50	a	0.348	0.379	0.847
51	b	0.293	0.2	0.517
52	c	0.94	0.799	0.212
53	d	0.518	0.833	0.943
54	e	0.614	0.43	0.726
55	f	0.148	0.015	0.906
Master

Cell Formulas
Range	Formula
L2:O7	L2	=LET(u,UNIQUE(A2:A55),SORT(HSTACK(u,COUNTIFS(A:A,u,B:B,"<0.3"),COUNTIFS(A:A,u,C:C,"<0.5",C:C,">0.3"),COUNTIFS(A:A,u,D:D,">0.5")),4,-1))
Dynamic array formulas.

 

[Afro_Cookie]

How about

Fluff.xlsm
	A	B	C	D							K	L	M	N	O
1	Subject	Score 1	Score 2	Score 3								Subject	Score 1	Score 2	Score 3
2	a	0.609	0.841	0.629							1	a	5	1	6
3	b	0.425	0.278	0.496							2	d	2	1	5
4	c	0.855	0.77	0.385							3	e	2	4	5
5	d	0.849	0.798	0.002							4	f	3	4	5
6	e	0.505	0.406	0.75							5	b	5	1	4
7	f	0.332	0.365	0.055							6	c	2	2	3
8	a	0.591	0.775	0.462
9	b	0.049	0.415	0.629
10	c	0.294	0.661	0.771
11	d	0.443	0.504	0.828
12	e	0.611	0.322	0.394
13	f	0.899	0.687	0.302
14	a	0.226	0.919	0.649
15	b	0.269	0.22	0.755
16	c	0.606	0.824	0.515
17	d	0.032	0.25	0.506
18	e	0.263	0.538	0.176
19	f	0.29	0.899	0.947
20	a	0.151	0.812	0.398
21	b	0.978	0.973	0.114
22	c	0.441	0.469	0.537
23	d	0.734	0	0.367
24	e	0.63	0.942	0.819
25	f	0.593	0.167	0.971
26	a	0.852	0.68	0.414
27	b	0.225	0.242	0.482
28	c	0.717	0.896	0.414
29	d	0.644	0.449	0.068
30	e	0.963	0.684	0.803
31	f	0.751	0.387	0.243
32	a	0.059	0.861	0.581
33	b	0.407	0.164	0.374
34	c	0.365	0.305	0.449
35	d	0.757	0.965	0.942
36	e	0.981	0.833	0.218
37	f	0.262	0.402	0.983
38	a	0.163	0.633	0.894
39	b	0.035	0.613	0.327
40	c	0.41	0.528	0.289
41	d	0.11	0.625	0.945
42	e	0.844	0.04	0.556
43	f	0.653	0.38	0.985
44	a	0.013	0.661	0.72
45	b	0.763	0.286	0.853
46	c	0.025	0.009	0.328
47	d	0.928	0.083	0.105
48	e	0.057	0.425	0.196
49	f	0.597	0.726	0.281
50	a	0.348	0.379	0.847
51	b	0.293	0.2	0.517
52	c	0.94	0.799	0.212
53	d	0.518	0.833	0.943
54	e	0.614	0.43	0.726
55	f	0.148	0.015	0.906
Master

Cell Formulas
Range	Formula
L2:O7	L2	=LET(u,UNIQUE(A2:A55),SORT(HSTACK(u,COUNTIFS(A:A,u,B:B,"<0.3"),COUNTIFS(A:A,u,C:C,"<0.5",C:C,">0.3"),COUNTIFS(A:A,u,D:D,">0.5")),4,-1))
Dynamic array formulas.

Thank you Fluff! You never dissapoint. I appreciate the help.

 

[Fluff]

You're welcome & thanks for the feedback.