Hi there !
Thanks for your various answers !
I am having the same type of issue, but with multiple criteria
Objective: retrieve the name of the state (row D16:AC16), that has the maximum/highest value (found in C20:AC120) from a selected 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 |
---|
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 |
---|
|
---|