Xlookup with multiple conditions

[zinah]

Hi,

I have the below table and what I need is below:

When I choose any team from the dropdown list, I will get the top 3 highest and lowest score for the selected team, then sort the results from lowest to highest for lowest score and highest to lowest for the highest score. I put the desired results table as an example when I select team 1 what should be the result. Can anyone help?

Xlookup Help with multi criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q
1														Desired Result Table
2		Team 1	Team 2	Team 3	Team 4	Team 5	Team 6	Team 7	Team 8	Team 9	Team 10				Team 1
3	Category 1	40	35	40	43	47	46	46	52	52	55
4	Category 2	35	25	40	41	46	47	46	50	48	46				Category Name	Score
5	Category 3	40	44	37	48	43	43	47	51	50	57			Lowest 3 Score	Category 2	35
6	Category 4	41	25	37	34	44	42	39	51	49	54				Category 1	40
7	Category 5	45	46	47	50	55	53	52	57	60	62				Category 10	40
8	Category 6	52	57	52	55	58	58	59	57	63	61				Category 3	40
9	Category 7	46	48	49	47	52	54	51	54	60	50				Category 4	41
10	Category 8	58	65	54	62	64	62	66	59	66	71				Category 13	41
11	Category 9	47	52	50	53	54	56	54	55	62	66
12	Category 10	40	44	46	51	51	48	43	51	56	57			Highest 3 Score	Category 6	52
13	Category 11	55	63	53	61	54	64	70	58	73	79				Category 11	55
14	Category 12	50	57	52	56	58	61	56	59	65	71				Category 8	58
15	Category 13	41	44	47	43	52	51	45	52	53	57
16	Category 14	48	60	55	55	61	61	66	65	74	77
17
18
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
O2	List	=$B$2:$K$2

 

[Cubist]

Here's an option.

Book1
	N	O	P
1	Desired Result Table
2		Team 1
3
4		Category Name	Score
5	Lowest 3 Score	Category 2	35
6		Category 1	40
7		Category 3	40
8		Category 10	40
9		Category 4	41
10		Category 13	41
11
12	Highest 3 Score	Category 6	52
13		Category 11	55
14		Category 8	58
Sheet5

Cell Formulas
Range	Formula
O5:P10	O5	=LET( f,FILTER(B3:K16,B2:K2=O2), h,HSTACK(A3:A16,f), SORT(FILTER(h,CHOOSECOLS(h,2)<=CHOOSEROWS(SORT(UNIQUE(f)),3)),2))
O12:P14	O12	=LET( f,FILTER(B3:K16,B2:K2=O2), h,HSTACK(A3:A16,f), SORT(FILTER(h,CHOOSECOLS(h,2)>=CHOOSEROWS(SORT(UNIQUE(f),,-1),3)),2))
Dynamic array formulas.

 

[zinah]

Here's an option.

Book1
	N	O	P
1	Desired Result Table
2		Team 1
3
4		Category Name	Score
5	Lowest 3 Score	Category 2	35
6		Category 1	40
7		Category 3	40
8		Category 10	40
9		Category 4	41
10		Category 13	41
11
12	Highest 3 Score	Category 6	52
13		Category 11	55
14		Category 8	58
Sheet5

Cell Formulas
Range	Formula
O5:P10	O5	=LET( f,FILTER(B3:K16,B2:K2=O2), h,HSTACK(A3:A16,f), SORT(FILTER(h,CHOOSECOLS(h,2)<=CHOOSEROWS(SORT(UNIQUE(f)),3)),2))
O12:P14	O12	=LET( f,FILTER(B3:K16,B2:K2=O2), h,HSTACK(A3:A16,f), SORT(FILTER(h,CHOOSECOLS(h,2)>=CHOOSEROWS(SORT(UNIQUE(f),,-1),3)),2))
Dynamic array formulas.

Amazing solution, thanks for this great formula.
How about if I need to get the lowest and highest sub category within a selected team and category? I put an example of desired result:

Xlookup Help with multi criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T
1														Desired Result Table						Category Name
2		Team 1	Team 2	Team 3	Team 4	Team 5	Team 6	Team 7	Team 8	Team 9	Team 10				Team 1					Category 1
3	Category 1	40	35	40	43	47	46	46	52	52	55									Category 2
4	a	35	25	40	41	46	47	46	50	48	46				Category Name	Score				Category 3
5	b	40	44	37	48	43	43	47	51	50	57			Lowest 3 Score	Category 2	35				Category 4
6	c	41	25	37	34	44	42	39	51	49	54				Category 1	40				Category 5
7	d	45	46	47	50	55	53	52	57	60	62				Category 10	40				Category 6
8	Category 2	52	57	52	55	58	58	59	57	63	61				Category 3	40				Category 7
9	e	46	48	49	47	52	54	51	54	60	50				Category 4	41				Category 8
10	f	58	65	54	62	64	62	66	59	66	71				Category 13	41				Category 9
11	Category 3	47	52	50	53	54	56	54	55	62	66									Category 10
12	g	40	44	46	51	51	48	43	51	56	57			Highest 3 Score	Category 6	52				Category 11
13	h	55	63	53	61	54	64	70	58	73	79				Category 11	55				Category 12
14	i	50	57	52	56	58	61	56	59	65	71				Category 8	58				Category 13
15	j	41	44	47	43	52	51	45	52	53	57									Category 14
16	Category 4	48	60	55	55	61	61	66	65	74	77
17	k	43	69	51	47	60	57	62	56	69	62
18	l	39	60	55	59	63	60	53	66	71	62			Team 1	Lowest score sub category	Highest score sub category
19	Category 5	55	54	58	59	59	65	69	64	73	92			Category 1	35	45
20	m	34	42	48	42	53	58	61	69	76	77
21	n	70	75	62	67	70	64	85	73	79	92
22	Category 6	54	54	58	61	60	63	65	65	65	76
23	o	40	40	45	49	46	53	53	44	52	64
24	p	45	40	52	53	54	56	51	62	57	79
25	Category 7	41	46	55	47	54	64	66	64	63	64
26	q	63	63	62	72	66	63	68	70	68	86
27	r	41	44	47	43	52	51	45	52	53	57
28	s	48	60	55	55	61	61	66	65	74	77
29	Category 8	39	38	46	52	57	55	45	49	60	43
30	t	50	38	44	51	51	47	48	45	53	50
31	u	43	40	43	51	57	51	52	53	57	71
32	Category 9	57	43	55	56	63	60	64	59	66	79
33	y	48	46	50	53	56	54	50	51	59	85
34	x	57	62	63	65	65	71	70	74	74	75
35	w	50	57	57	57	64	67	76	73	78	64
36	z	41	46	48	50	54	61	56	62	66	79
37	Category 10	62	62	67	72	67	72	63	72	69	71
38	a1	75	85	79	83	75	82	84	88	83	86
39	a2	35	42	53	45	55	63	59	62	66	60
40	a3	35	29	46	37	47	53	57	58	62	43
41	a4	34	46	57	43	54	71	60	66	74	79
42	Category 11	30	48	55	61	67	70	67	65	70	54
43	b1	39	52	55	48	57	67	60	63	70	71
44	b2	37	44	53	42	56	60	59	62	64	54
45	b3	34	33	52	38	51	59	49	60	57	57
46	Category 12	72	73	71	79	71	73	79	79	79	85
47	c1	60	53	60	70	61	61	62	69	66	69
48	c2	70	76	68	78	69	71	78	78	75	85
49	c3	75	78	69	81	70	72	80	78	82	85
50	Category 13	74	75	73	77	73	78	85	83	85	85
51	d1	84	84	84	90	83	85	89	89	89	100
52	d2	82	90	82	86	82	80	80	87	83	93
53	Category 14	82	90	80	85	81	78	72	86	81	93
54	e1	82	90	80	87	79	76	84	84	81	86
55	e2	82	90	84	87	83	84	76	88	86	93
56	e3	79	85	81	85	83	80	81	88	80	93
57	e4	86	92	85	88	86	84	88	88	89	100
58
Sheet1

Cells with Data Validation
Cell	Allow	Criteria
O2	List	=$B$2:$K$2

 

[Cubist]

You've changed your data a bit. Are you looking for the top or bottom part or both?
In the bottom, are you looking for: given a Category and Team what's the highest/lowest within sub-categories within that category?

 

[zinah]

You've changed your data a bit. Are you looking for the top or bottom part or both?
In the bottom, are you looking for: given a Category and Team what's the highest/lowest within sub-categories within that category?

Yes, I added further details which is the sub category.
To answer your question: yes, I'm looking for a given category and team (highest and bottom) but not the top 3 or bottom 3, only one.

 

[Cubist]

Are the headers a literal "Category 1,2,3,..." or they're named something different? If they're named something different, how can you tell which lines are the categories and which are the sub-categories?