INDEX MATCH MAXIFS / AGGREGATE - Multiple criteria

[lagardem]

Hi there !

Hope you're all doing good !

Objective: I would like to retrieve the name of the state (row D16:AC16), that has the maximum/highest value (found in C20:AC120), for a some criteria (A6 to A10 per ex), selected within column A (A19:A124).

The search thus goes horizontally/vertically/left/right

Any idea of how it could be solved ?

Thanks a lot in advance !

Cheers

NB: I tried these, but I failed somehow :P :
=XLOOKUP(($A$20:$A$124=A6)*(MAX(IF($A$20:$A$124=A6;$C$20:$AC$124));$C$16:$AC$16))
=INDEX($C$16:$AC$16;MATCH(MAXIFS($C$19:$AC$124;$A$19:$A$124;A6);$C$19:$AC$124;0))
=INDEX($C$16:$AC$16;MATCH(MAX(IF($A$19:$A$124=A6;$C$19:$AC$124));$C$19:$AC$124;0))
=INDEX($C$16:$AC$16;MATCH(MAX(IF($A$19:$A$124=A7;$C$19:$AC$124));$C$19:$AC$124;0))
=INDEX($16:$16;AGGREGATE(15;6;COLUMN($C$16:$AC$16)/(INDEX($C$20:$AC$124;MATCH(LOOKUP(0;0/FREQUENCY(0;ABS($A$20:$A$124-A6));$A$20:$A$124);$A$20:$A$124;0)=INDEX($AD$20:$AD$124;MATCH(A6;$A$20:$A$124;0));1))))
=INDEX($14:$14;AGGREGATE(15;6;COLUMN($C$14:$AC$14)/(INDEX($C$20:$AC$124;MATCH(LOOKUP(0;0/FREQUENCY(0;ABS($A$20:$A$124-A6));$A$20:$A$124);$A$20:$A$124;0)=E6;1))))

INDEX MATCH MAXIFS_Multiple criteria.xlsx
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC	AD	AE	AF	AG
1
2	How could I retrieve the state with the highest values of the below criteria (D6 to D10), all at once, without adding columns AD and following ?
3
4
5		Highest value INDEX MATCH	State INDEX MATCH	All at once formula ? :)
6	Surfaces agricoles en %	56	FR
7	Surfaces boisées en %	51	TI
8	par km²	5 300	BS
9	selon le Code pénal (CP)	109	BS
10	selon la Loi sur les stupéfiants et les produits psychotropes (LStup)	18	BS
11
12
13
14	RAW DATA																													ADDED COLUMNS
15
16	Choix d'indicateurs en comparaison régionale, 2021	Année	Suisse	ZH	BE	LU	UR	SZ	OW	NW	GL	ZG	FR	SO	BS	BL	SH	AR	AI	SG	GR	AG	TG	TI	VD	VS	NE	GE	JU	MAX	Related state	MIN	Related state
17
18
19	Population
20	Habitants en milliers	2019	8 606.0	1 539.3	1 039.5	413.1	36.7	160.5	37.9	43.1	40.6	127.6	321.8	275.2	195.8	289.5	82.3	55.4	16.1	510.7	199.0	685.8	279.5	351.5	805.1	345.5	176.5	504.1	73.6	1 539	ZH	16	AI
21	Variation en %	2010-2019	9.4	12.1	6.1	9.4	3.6	9.4	6.6	5.0	5.1	12.9	15.5	7.8	5.9	5.5	7.8	4.6	2.8	6.6	3.3	12.2	12.5	5.3	12.9	10.5	2.6	10.1	5.1	16	FR	3	NE
22	par km²	2019	215.2	926.8	178.0	289.1	34.7	188.5	78.9	178.5	59.6	616.2	202.0	348.2	5 300.2	559.2	276.2	228.3	93.5	261.8	28.0	491.6	323.9	128.2	285.4	66.3	246.3	2 050.8	87.8	5 300	BS	28	GR
23	Répartition par âge en %	2019																												-	N/A, nfwd	-	N/A, nfwd
24	0–19		20.0	19.8	19.0	20.3	20.0	19.3	20.0	18.1	19.2	20.3	22.2	19.0	17.3	19.2	18.7	20.2	21.1	20.5	17.8	20.2	20.3	18.0	21.9	19.6	21.0	21.0	20.8	22	FR	17	BS
25	20–64		61.4	63.2	59.9	61.9	59.2	62.6	60.7	61.0	60.5	62.4	61.8	61.1	62.9	58.7	59.9	59.9	59.3	61.0	60.5	61.8	61.8	59.1	61.5	60.5	59.8	62.5	57.9	63	ZH	58	JU
26	65 ou plus		18.7	17.0	21.1	17.8	20.8	18.2	19.3	20.9	20.4	17.3	16.0	19.9	19.8	22.2	21.4	19.9	19.6	18.5	21.7	17.9	17.9	23.0	16.6	19.9	19.3	16.4	21.3	23	TI	16	FR
27	Population urbaine en % 1)	2019	84.8	99.3	74.7	63.9	88.6	82.1	27.6	50.5	76.7	100.0	74.8	85.9	100.0	97.6	89.7	76.6	0.0	82.4	44.9	85.0	67.0	92.8	89.6	78.9	87.8	100.0	53.7	100	ZG	-	AI
28	Etrangers en %	2019	25.3	27.1	16.6	18.8	12.6	21.9	14.7	14.8	24.2	28.3	22.8	22.9	36.6	23.1	26.1	16.5	11.3	24.4	18.8	25.3	25.2	27.6	33.0	22.6	25.3	40.0	14.7	40	GE	11	AI
29	Mouvement de la population (en ‰)	2019																												-	N/A, nfwd	-	N/A, nfwd
30	Taux brut de nuptialité		4.5	5.2	4.4	4.8	4.8	4.6	5.0	4.3	4.3	4.9	4.2	4.6	4.9	4.4	4.9	5.0	4.9	5.0	4.3	4.7	4.9	3.4	3.9	4.2	3.6	4.3	3.7	5	ZH	3	TI
31	Taux brut de divortialité		2.0	2.0	1.9	1.6	1.3	1.9	1.4	2.1	1.4	1.9	2.0	1.8	2.0	1.8	2.0	2.0	1.0	1.9	1.7	2.0	1.9	2.1	2.1	2.1	2.2	2.3	2.3	2	JU	1	AI
32	Taux brut de natalité		10.0	10.8	9.6	10.6	9.4	9.6	9.2	8.9	10.2	10.4	10.9	9.5	10.5	8.8	8.8	10.1	10.8	10.3	8.2	10.3	10.4	7.1	10.7	9.1	9.5	10.7	9.6	11	FR	7	TI
33	Taux brut de mortalité		7.9	7.4	9.3	7.5	8.4	6.9	7.4	7.8	10.3	6.2	6.6	8.4	10.6	8.6	9.8	8.7	8.3	7.9	8.8	7.3	7.5	9.2	6.8	8.4	9.0	6.7	9.1	11	BS	6	ZG
34	Ménages privés en milliers	2019	3 811	692	475	179	15	69	16	19	18	55	134	123	97	129	38	24	7	223	92	297	122	164	354	153	82	203	32	3 811	N/A, nfwd	7	AI
35	Taille moyenne des ménages en personnes	2019	2.2	2.2	2.1	2.3	2.3	2.3	2.3	2.2	2.2	2.3	2.4	2.2	1.9	2.2	2.1	2.3	2.4	2.3	2.1	2.3	2.3	2.1	2.2	2.2	2.1	2.4	2.2	2	AI	2	BS
36																														-	N/A, nfwd	-	N/A, nfwd
37	Langue principale en % 2) 3)	2019																												-	N/A, nfwd	-	N/A, nfwd
38	Allemand		62.7	80.7	83.7	88.6	93.4	88.3	92.0	90.6	86.5	80.3	26.1	87.1	76.8	86.2	86.8	90.8	94.9	87.7	75.2	86.4	89.1	10.2	5.5	24.6	4.7	4.0	7.4	95	AI	4	GE
39	Français		22.8	3.3	11.0	1.6	X	1.9	(1.5)	(1.6)	(1.2)	3.4	69.0	2.9	5.0	3.2	(1.8)	(1.1)	X	1.3	1.3	2.0	1.3	4.7	82.6	67.8	87.7	79.6	89.8	90	JU	1	AR
40	Italien		8.3	5.6	3.1	3.3	(1.4)	3.5	(1.2)	(3.2)	5.5	4.3	2.4	4.8	6.1	5.5	3.9	(2.3)	(1.5)	3.5	13.2	5.1	3.7	88.6	5.0	4.6	5.7	6.4	2.5	89	TI	1	OW
41	Romanche		0.5	0.3	(0.1)	(0.2)	X	(0.3)	(0.6)	X	(0.6)	(0.2)	(0.1)	(0.2)	(0.2)	(0.1)	X	X	X	(0.4)	14.7	(0.1)	(0.1)	(0.1)	(0.0)	X	X	(0.1)	X	15	GR	0	VD
42	Anglais		6.5	9.5	4.4	4.6	(3.3)	6.3	(3.5)	5.4	(4.0)	12.1	3.9	4.3	12.1	6.7	5.2	4.6	(3.3)	4.1	3.4	5.5	3.9	3.9	8.9	3.8	4.6	12.2	(2.3)	12	GE	2	JU
43																														-	N/A, nfwd	-	N/A, nfwd
44	Appartenance à une religion en % 2)	2019																												-	N/A, nfwd	-	N/A, nfwd
45	Catholiques romains		34.4	24.9	15.4	58.8	74.8	56.5	68.1	62.7	30.1	48.0	58.0	31.2	14.9	26.2	21.1	27.7	74.0	42.0	40.2	30.2	30.0	62.8	27.7	68.9	19.7	31.0	64.3	75	UR	15	BS
46	Evangéliques réformés		22.5	26.7	47.5	9.4	(5.0)	11.3	10.4	11.3	30.9	12.7	11.8	18.8	14.6	27.6	32.6	37.6	(9.2)	19.3	31.5	22.2	29.8	3.9	21.4	5.6	19.2	8.2	9.8	48	BE	4	TI
47	Sans appartenance religieuse		29.5	32.7	24.0	20.9	14.7	21.8	15.8	20.2	24.8	26.6	20.8	35.9	52.8	32.5	28.7	21.7	(10.9)	22.0	21.3	31.7	25.4	24.2	36.4	17.8	48.8	44.3	17.3	53	BS	11	AI
Data OFS

Cell Formulas
Range	Formula
B6:B10	B6	=INDEX($AD$20:$AD$124,MATCH(A6,$A$20:$A$124,0))
C6:C10	C6	=INDEX($AE$20:$AE$124,MATCH(A6,$A$20:$A$124,0))
AD20	AD20	=MAX(D20:AC20)
AE20:AE47	AE20	=IFERROR(INDEX($D$16:$AC$16,MATCH(AD20,D20:AC20,0)),"N/A, nfwd")
AF20:AF47	AF20	=MIN(C20:AC20)
AG20:AG47	AG20	=IFERROR(INDEX($D$16:$AC$16,MATCH(AF20,D20:AC20,0)),"N/A, nfwd")
AD21:AD47	AD21	=MAX(C21:AC21)

 

[Fluff]

How about

Fluff.xlsm
	A	B	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U	V	W	X	Y	Z	AA	AB	AC
1
2
3
4
5		Highest value INDEX MATCH	State INDEX MATCH	All at once formula ? :)
6	Surfaces agricoles en %	100.0	ZG	ZG
7	Surfaces boisées en %	47.5	BE	BE
8	par km²	5300.2	BS	BS
9	selon le Code pénal (CP)	40.0	GE	GE
10	selon la Loi sur les stupéfiants et les produits psychotropes (LStup)	0.0	N/A, nfwd	N/A, nfwd
11
12
13
14	RAW DATA
15
16	Choix d'indicateurs en comparaison régionale, 2021	Année	Suisse	ZH	BE	LU	UR	SZ	OW	NW	GL	ZG	FR	SO	BS	BL	SH	AR	AI	SG	GR	AG	TG	TI	VD	VS	NE	GE	JU
17
18
19	Population
20	Habitants en milliers	2019	8606.0	1539.3	1039.5	413.1	36.7	160.5	37.9	43.1	40.6	127.6	321.8	275.2	195.8	289.5	82.3	55.4	16.1	510.7	199.0	685.8	279.5	351.5	805.1	345.5	176.5	504.1	73.6
21	Variation en %	2010-2019	9.4	12.1	6.1	9.4	3.6	9.4	6.6	5.0	5.1	12.9	15.5	7.8	5.9	5.5	7.8	4.6	2.8	6.6	3.3	12.2	12.5	5.3	12.9	10.5	2.6	10.1	5.1
22	par km²	2019	215.2	926.8	178.0	289.1	34.7	188.5	78.9	178.5	59.6	616.2	202.0	348.2	5300.2	559.2	276.2	228.3	93.5	261.8	28.0	491.6	323.9	128.2	285.4	66.3	246.3	2050.8	87.8
23	Répartition par âge en %	2019
24	0–19		20.0	19.8	19.0	20.3	20.0	19.3	20.0	18.1	19.2	20.3	22.2	19.0	17.3	19.2	18.7	20.2	21.1	20.5	17.8	20.2	20.3	18.0	21.9	19.6	21.0	21.0	20.8
25	20–64		61.4	63.2	59.9	61.9	59.2	62.6	60.7	61.0	60.5	62.4	61.8	61.1	62.9	58.7	59.9	59.9	59.3	61.0	60.5	61.8	61.8	59.1	61.5	60.5	59.8	62.5	57.9
26	65 ou plus		18.7	17.0	21.1	17.8	20.8	18.2	19.3	20.9	20.4	17.3	16.0	19.9	19.8	22.2	21.4	19.9	19.6	18.5	21.7	17.9	17.9	23.0	16.6	19.9	19.3	16.4	21.3
27	Surfaces agricoles en %	2019	84.8	99.3	74.7	63.9	88.6	82.1	27.6	50.5	76.7	100.0	74.8	85.9	100.0	97.6	89.7	76.6	0.0	82.4	44.9	85.0	67.0	92.8	89.6	78.9	87.8	100.0	53.7
28	selon le Code pénal (CP)	2019	25.3	27.1	16.6	18.8	12.6	21.9	14.7	14.8	24.2	28.3	22.8	22.9	36.6	23.1	26.1	16.5	11.3	24.4	18.8	25.3	25.2	27.6	33.0	22.6	25.3	40.0	14.7
29	selon la Loi sur les stupéfiants et les produits psychotropes (LStup)	2019
30	Taux brut de nuptialité		4.5	5.2	4.4	4.8	4.8	4.6	5.0	4.3	4.3	4.9	4.2	4.6	4.9	4.4	4.9	5.0	4.9	5.0	4.3	4.7	4.9	3.4	3.9	4.2	3.6	4.3	3.7
31	Taux brut de divortialité		2.0	2.0	1.9	1.6	1.3	1.9	1.4	2.1	1.4	1.9	2.0	1.8	2.0	1.8	2.0	2.0	1.0	1.9	1.7	2.0	1.9	2.1	2.1	2.1	2.2	2.3	2.3
32	Taux brut de natalité		10.0	10.8	9.6	10.6	9.4	9.6	9.2	8.9	10.2	10.4	10.9	9.5	10.5	8.8	8.8	10.1	10.8	10.3	8.2	10.3	10.4	7.1	10.7	9.1	9.5	10.7	9.6
33	Taux brut de mortalité		7.9	7.4	9.3	7.5	8.4	6.9	7.4	7.8	10.3	6.2	6.6	8.4	10.6	8.6	9.8	8.7	8.3	7.9	8.8	7.3	7.5	9.2	6.8	8.4	9.0	6.7	9.1
34	Ménages privés en milliers	2019	3811.3	691.7	474.6	179.3	15.5	69.3	16.2	19.1	18.1	54.9	133.5	122.9	97.5	128.7	37.7	24.0	6.5	223.0	92.4	297.3	121.7	163.7	354.5	152.6	81.5	202.6	32.4
35	Taille moyenne des ménages en personnes	2019	2.2	2.2	2.1	2.3	2.3	2.3	2.3	2.2	2.2	2.3	2.4	2.2	1.9	2.2	2.1	2.3	2.4	2.3	2.1	2.3	2.3	2.1	2.2	2.2	2.1	2.4	2.2
36
37	Langue principale en % 2) 3)	2019
38	Allemand		62.7	80.7	83.7	88.6	93.4	88.3	92.0	90.6	86.5	80.3	26.1	87.1	76.8	86.2	86.8	90.8	94.9	87.7	75.2	86.4	89.1	10.2	5.5	24.6	4.7	4.0	7.4
39	Français		22.8	3.3	11.0	1.6	X	1.9	1.5	1.6	1.2	3.4	69.0	2.9	5.0	3.2	1.8	1.1	X	1.3	1.3	2.0	1.3	4.7	82.6	67.8	87.7	79.6	89.8
40	Italien		8.3	5.6	3.1	3.3	1.4	3.5	1.2	3.2	5.5	4.3	2.4	4.8	6.1	5.5	3.9	2.3	1.5	3.5	13.2	5.1	3.7	88.6	5.0	4.6	5.7	6.4	2.5
41	Romanche		0.5	0.3	0.1	0.2	X	0.3	0.6	X	0.6	0.2	0.1	0.2	0.2	0.1	X	X	X	0.4	14.7	0.1	0.1	0.1	0.0	X	X	0.1	X
42	Anglais		6.5	9.5	4.4	4.6	3.3	6.3	3.5	5.4	4.0	12.1	3.9	4.3	12.1	6.7	5.2	4.6	3.3	4.1	3.4	5.5	3.9	3.9	8.9	3.8	4.6	12.2	2.3
43
44	Appartenance à une religion en % 2)	2019
45	Catholiques romains		34.4	24.9	15.4	58.8	74.8	56.5	68.1	62.7	30.1	48.0	58.0	31.2	14.9	26.2	21.1	27.7	74.0	42.0	40.2	30.2	30.0	62.8	27.7	68.9	19.7	31.0	64.3
46	Surfaces boisées en %		22.5	26.7	47.5	9.4	5.0	11.3	10.4	11.3	30.9	12.7	11.8	18.8	14.6	27.6	32.6	37.6	9.2	19.3	31.5	22.2	29.8	3.9	21.4	5.6	19.2	8.2	9.8
47	Sans appartenance religieuse		29.5	32.7	24.0	20.9	14.7	21.8	15.8	20.2	24.8	26.6	20.8	35.9	52.8	32.5	28.7	21.7	10.9	22.0	21.3	31.7	25.4	24.2	36.4	17.8	48.8	44.3	17.3
Data

Cell Formulas
Range	Formula
B6:B10	B6	=INDEX($AD$20:$AD$124,MATCH(A6,$A$20:$A$124,0))
C6:C10	C6	=INDEX($AE$20:$AE$124,MATCH(A6,$A$20:$A$124,0))
D6:D10	D6	=LET(f,FILTER($D$20:$AC$120,$A$20:$A$120=A6),IF(MAX(f)=0,"N/A, nfwd",TAKE(SORTBY($D$16:$AC$16,f,-1),,1)))

 

[lagardem]

Amazing ! thanks for your quick reply !

 

[Fluff]

You're welcome & thanks for the feedback.