Les tableurs comme Microsoft Excel et Google Sheets proposent plusieurs fonctions pour rechercher des valeurs dans vos tableaux. Les deux proposent HLOOKUP et VLOOKUP pour les recherches horizontales et verticales, ainsi que XLOOKUP, qui les combine et offre plus de flexibilité. Toutefois, les utiliser avec plusieurs critères peut être très délicat, car cela demande plusieurs niveaux d'imbrication. Heureusement, combiner les fonctions INDEX et MATCH (EQUIV) vous permet d'effectuer des recherches souples et complexes à plusieurs critères.
Dans ce guide, vous commencerez par revoir les fonctions INDEX et MATCH et la façon de les combiner pour effectuer des recherches. Vous verrez ensuite comment un petit ajustement vous permet d'utiliser plusieurs critères dans vos recherches, qu'elles soient verticales, horizontales ou mixtes. Vous trouverez des instructions pas à pas pour rechercher des valeurs avec plusieurs critères dans une recherche horizontale, une recherche verticale et une recherche avec des critères horizontaux et verticaux. Enfin, vous apprendrez à combiner INDEX et MATCH avec d'autres fonctions, comme AVERAGE (MOYENNE).
Syntaxe des fonctions INDEX et MATCH
Avant de vous lancer dans les recherches à plusieurs critères, il est utile de revoir la syntaxe des deux fonctions, ainsi que la syntaxe combinée.
=INDEX(reference, row*, column*)
- reference : la plage de cellules dont vous voulez renvoyer les valeurs.
- row* : facultatif. L'index de la ligne à renvoyer dans la plage de cellules de référence. 0 par défaut. C'est ici que vous ajoutez MATCH pour les recherches verticales.
- column* : facultatif. L'index de la colonne à renvoyer dans la plage de cellules de référence. 0 par défaut. C'est ici que vous ajoutez MATCH pour les recherches horizontales.
=MATCH(search_key, range, search_type*)
- search_key : la valeur que vous voulez rechercher.
- range : le tableau à une dimension dans lequel effectuer la recherche.
- search_type* : facultatif. La méthode de recherche.
- 1 est la valeur par défaut. La fonction considère que la plage est triée par ordre croissant et renvoie la plus grande valeur inférieure ou égale à la clé de recherche.
- 0 trouve une correspondance exacte. La plage n'a pas besoin d'être triée.
- -1 considère que la plage est triée par ordre décroissant et renvoie la plus petite valeur supérieure ou égale à la clé de recherche.
Syntaxe combinée d'INDEX et MATCH
Pour utiliser la fonction INDEX, vous devez indiquer la ligne et la colonne de votre plage. La fonction renvoie alors le contenu de la cellule située à l'intersection de cette ligne et de cette colonne. Autrement dit, vous devez fournir les coordonnées ou les références de cellule.
La fonction MATCH, elle, recherche une valeur précise et renvoie les coordonnées ou la référence de cellule. C'est précisément ce qu'il vous faut pour rendre la fonction INDEX dynamique. Comme indiqué plus haut, vous pouvez remplacer les arguments de ligne et de colonne d'INDEX par des fonctions MATCH.
=INDEX(range,MATCH(search_key,search_column,search_type),MATCH(search_key,search_row,search_type))
Avec la formule ci-dessus, vous pouvez indiquer le critère de ligne et le critère de colonne de votre recherche, mais vous êtes limité à un seul de chaque. Heureusement, un petit ajustement de la formule permet de changer cela.
Syntaxe combinée pour les recherches à plusieurs critères
Dans la fonction MATCH, vous pouvez remplacer search_key par "1" et search_column ou search_row par un ensemble de critères ou de conditions. Chaque condition doit être entre parenthèses et reliée aux autres par un signe de multiplication. La fonction évalue ainsi chaque condition et ne renvoie "1" que si toutes sont remplies.
Recherche verticale à plusieurs critères
Utilisez la formule suivante pour une recherche verticale à plusieurs critères.
=INDEX(reference,MATCH(1,(criteria1)*(criteria2)*(criteriaN),0))
Recherche horizontale à plusieurs critères
Utilisez la formule suivante pour une recherche horizontale à plusieurs critères.
=INDEX(reference,row,MATCH(1,(criteria1)*(criteria2)*(criteriaN),0))
Recherche verticale et horizontale à plusieurs critères
Utilisez la formule suivante pour une recherche avec plusieurs critères verticaux et horizontaux.
=INDEX(reference,MATCH(1,(criteria1)*(criteria2)*(criteriaN),0),MATCH(1,(criteria1)*(criteria2)*(criteriaN),0))
Comment utiliser INDEX et MATCH avec plusieurs critères dans Google Sheets
Combiner les fonctions INDEX et MATCH vous permet d'effectuer des recherches qui trouvent des valeurs selon plusieurs critères. Autrement dit, vous pouvez lever l'ambiguïté lorsqu'une même valeur a plusieurs correspondances possibles. Ces recherches peuvent être verticales, horizontales ou les deux.
Heureusement, les deux fonctions sont disponibles dans Microsoft Excel et Google Sheets. Vous trouverez ci-dessous les instructions pour effectuer une recherche avec deux critères : un vertical et un horizontal. La section suivante présente des exemples avec plusieurs critères verticaux ou horizontaux. Enfin, un dernier exemple combine INDEX, MATCH et AVERAGE.
1. Définissez les critères
1. Ouvrez dans Google Sheets la feuille de calcul où vous voulez les résultats de la recherche. Ici, je les veux dans la même feuille que les données, mais vous pouvez utiliser IMPORTRANGE pour rechercher dans d'autres fichiers.
2. Saisissez les critères, ou les valeurs à rechercher, dans des cellules distinctes. Ici, le premier critère indique la ligne et le second, la colonne.
Vous pouvez facilement ajouter des listes déroulantes dans les cellules de critères pour les modifier plus simplement. Pour apprendre à ajouter des listes déroulantes dans Google Sheets, consultez ce guide sur comment ajouter une liste déroulante dans Google Sheets.
2. Fonction INDEX
1. Saisissez la formule INDEX dans la cellule où vous voulez le résultat de la recherche.
2. Comme premier argument, sélectionnez la plage de cellules qui contient le tableau.
3. Fonction MATCH : verticale
1. Comme deuxième argument d'INDEX, saisissez la fonction MATCH.
2. Comme premier argument, sélectionnez la cellule contenant la valeur de recherche verticale.
3. Comme deuxième argument, sélectionnez la colonne dans laquelle vous voulez effectuer la recherche.
4. Le troisième argument doit être "0", car vous voulez une correspondance exacte. Fermez la parenthèse et ajoutez une virgule.
4. Fonction MATCH : horizontale
1. Enfin, ajoutez une autre fonction MATCH. C'est le troisième paramètre de la fonction INDEX, celui qui indique la colonne.
2. Sélectionnez la cellule contenant la valeur de recherche horizontale comme premier argument.
3. Sélectionnez la ligne dans laquelle vous voulez effectuer la recherche comme deuxième argument.
4. Enfin, saisissez "0" comme troisième argument et ajoutez deux parenthèses fermantes.
5. Appuyez sur "Entrée" pour voir le résultat.
6. La fonction se recalcule automatiquement dès que vous modifiez les critères.
INDEX et MATCH à plusieurs critères : exemples
Cette section présente deux exemples. Dans le premier, vous trouverez une correspondance à l'aide de plusieurs critères verticaux et d'un critère horizontal. Dans le second, vous combinerez INDEX et MATCH avec AVERAGE pour obtenir le prix moyen d'un produit donné chez un fournisseur précis.
Recherche avec plusieurs critères verticaux et un critère horizontal
1. Ouvrez dans Google Sheets la feuille de calcul qui contient vos données.
2. Saisissez les critères dans des cellules distinctes. J'ai ajouté des listes déroulantes à ces cellules pour faciliter le choix des critères.
3. Dans la cellule où vous voulez le résultat, saisissez la fonction INDEX et sélectionnez le tableau comme premier paramètre.
4. Comme deuxième paramètre, ajoutez la fonction MATCH. Ajoutez "1" comme clé de recherche.
5. Après la virgule, ouvrez la parenthèse et ajoutez le critère ou la condition. Fermez la parenthèse et ajoutez un signe de multiplication.
6. Ouvrez une autre parenthèse pour ajouter le deuxième critère. Une fois terminé, fermez la parenthèse et ajoutez une virgule.
7. N'oubliez pas d'ajouter "0" pour une correspondance exacte, de fermer la parenthèse et d'ajouter une autre virgule.
8. Ajoutez une autre fonction MATCH. Comme vous n'avez qu'un seul critère horizontal, sélectionnez simplement la clé de recherche (le mois) et la ligne où elle doit être trouvée.
9. Enfin, saisissez "0" pour une correspondance exacte et fermez les deux parenthèses.
10. Appuyez sur "Entrée" pour voir le résultat.
Recherche à plusieurs critères et AVERAGE
Dans cet exemple, vous apprendrez à obtenir le prix moyen d'un produit et d'un fournisseur donnés sur les six mois de données dont vous disposez.
1. Dans la feuille de calcul où vous voulez le résultat, saisissez les critères dans des cellules distinctes.
2. Dans la cellule où vous voulez le résultat, saisissez la fonction AVERAGE.
3. Ensuite, saisissez la fonction INDEX.
4. Sélectionnez le tableau comme premier paramètre de la fonction INDEX.
5. Ajoutez la fonction MATCH et saisissez "1" comme clé de recherche.
6. Après la virgule, ajoutez chacun de vos critères verticaux entre parenthèses, reliés par un signe de multiplication.
7. Après la virgule, ajoutez "0" pour une correspondance exacte et fermez les trois parenthèses.
8. Appuyez sur "Entrée" pour voir le résultat.
Conclusion
INDEX et MATCH offrent une façon plus souple d'effectuer des recherches dans Microsoft Excel et Google Sheets. Vous pouvez rechercher rapidement des valeurs dans des tableaux complexes à l'aide de plusieurs critères verticaux et horizontaux. Vous savez maintenant utiliser INDEX et MATCH pour obtenir des valeurs selon des critères verticaux et horizontaux, y compris plusieurs de chaque type. Vous savez aussi combiner les fonctions INDEX et MATCH avec d'autres fonctions pour intégrer le calcul directement dans votre formule de recherche. Si vous devez additionner les valeurs de toutes les lignes qui remplissent vos conditions, plutôt que de renvoyer une seule correspondance, utilisez SUMIFS avec plusieurs critères.
Pour en savoir plus sur les autres fonctions de recherche, consultez ces guides :
English 
Español 
Português do Brasil 
Français