Comment créer une formule de recherche Excel avec plusieurs critères

Ce qu’il faut savoir

• Commencez par créer une fonction INDEX, puis démarrez la fonction MATCH imbriquée en entrant l’argument Lookup_value.
• Ensuite, ajoutez l’argument Lookup_array suivi de l’argument Match_type, puis spécifiez la plage de colonnes.
• Ensuite, transformez la fonction imbriquée en une formule matricielle en appuyant sur Ctrl + Maj + Entrée. Enfin, ajoutez les termes de recherche à la feuille de calcul.

Cet article explique comment créer une formule de recherche qui utilise plusieurs critères dans Excel pour rechercher des informations dans une base de données ou une table de données à l’aide d’une formule matricielle. La formule matricielle implique l’imbrication de la fonction MATCH dans la fonction INDEX. Les informations couvrent Excel pour Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 et Excel pour Mac.

Suivez le tutoriel

Pour suivre les étapes de ce didacticiel, entrez les exemples de données dans les cellules suivantes, comme indiqué dans l’image ci-dessous. Les lignes 3 et 4 sont laissées vides pour tenir compte de la formule matricielle créée au cours de ce didacticiel. (Notez que ce tutoriel n’inclut pas le formatage vu dans l’image.)

• Entrez la plage supérieure de données dans les cellules D1 à F2.
• Entrez la deuxième plage dans les cellules D5 à F11.

Créer une fonction INDEX dans Excel

La fonction INDEX est l’une des rares fonctions d’Excel à avoir plusieurs formulaires. La fonction a une forme matricielle et une forme de référence. La forme matricielle renvoie les données d’une base de données ou d’une table de données. Le formulaire de référence donne la référence de cellule ou l’emplacement des données dans le tableau.

Dans ce didacticiel, le formulaire Array est utilisé pour trouver le nom du fournisseur de widgets en titane, plutôt que la référence de cellule à ce fournisseur dans la base de données.

Suivez ces étapes pour créer la fonction INDEX:

1. Sélectionnez la cellule F3 pour en faire la cellule active. Cette cellule est l’endroit où la fonction imbriquée sera saisie.
2. Allez dans Formules.

   

3. Choisissez Recherche et référence pour ouvrir la liste déroulante des fonctions.
4. Sélectionnez INDEX pour ouvrir la boîte de dialogue Sélectionner les arguments.
5. Choisissez array,row_num,column_num.
6. Sélectionnez OK pour ouvrir la boîte de dialogue Arguments de la fonction. Dans Excel pour Mac, le générateur de formule s’ouvre.
7. Placez le curseur dans la zone de texte Tableau.
8. Mettez en surbrillance les cellules D6 à F11 dans la feuille de calcul pour entrer la plage dans la boîte de dialogue.
   Laissez la boîte de dialogue Arguments de la fonction ouverte. La formule n’est pas terminée. Vous compléterez la formule dans les instructions ci-dessous.
   

Démarrer la fonction MATCH imbriquée

Lors de l’imbrication d’une fonction dans une autre, il n’est pas possible d’ouvrir le générateur de formule de la seconde fonction, ou imbriquée, pour entrer les arguments nécessaires. La fonction imbriquée doit être entrée comme l’un des arguments de la première fonction.

Lors de la saisie manuelle des fonctions, les arguments de la fonction sont séparés les uns des autres par une virgule.

La première étape pour entrer dans la fonction MATCH imbriquée consiste à entrer l’argument Lookup_value. La Lookup_value est l’emplacement ou la référence de cellule pour le terme de recherche à mettre en correspondance dans la base de données.

La Lookup_value n’accepte qu’un seul critère ou terme de recherche. Pour rechercher plusieurs critères, étendez Lookup_value en concaténant ou en joignant deux ou plusieurs références de cellule à l’aide du symbole esperluette (&).

1. Dans la boîte de dialogue Arguments de la fonction, placez le curseur dans la zone de texte Row_num.
2. Entrez CORRESPONDANCE(.
3. Sélectionnez la cellule D3 pour entrer cette référence de cellule dans la boîte de dialogue.
4. Entrez & (l’esperluette) après la référence de cellule D3 pour ajouter une deuxième référence de cellule.
5. Sélectionnez la cellule E3 pour entrer la deuxième référence de cellule.
6. Entrez , (une virgule) après la référence de cellule E3 pour terminer la saisie de l’argument Lookup_value de la fonction MATCH.

   
   Dans la dernière étape du didacticiel, les Lookup_values ​​seront entrés dans les cellules D3 et E3 de la feuille de calcul.

Terminez la fonction MATCH imbriquée

Cette étape couvre l’ajout de l’argument Lookup_array pour la fonction MATCH imbriquée. Lookup_array est la plage de cellules que la fonction MATCH recherche pour trouver l’argument Lookup_value ajouté à l’étape précédente du didacticiel.

Étant donné que deux champs de recherche ont été identifiés dans l’argument Lookup_array, la même chose doit être faite pour Lookup_array. La fonction MATCH ne recherche qu’un seul tableau pour chaque terme spécifié. Pour entrer plusieurs tableaux, utilisez l’esperluette pour concaténer les tableaux ensemble.

1. Placez le curseur à la fin des données dans la zone de texte Row_num. Le curseur apparaît après la virgule à la fin de l’entrée actuelle.
2. Mettez en surbrillance les cellules D6 à D11 dans la feuille de calcul pour entrer la plage. Cette plage est le premier tableau que la fonction recherche.
3. Entrez & (une esperluette) après les références de cellule D6:D11. Ce symbole force la fonction à rechercher deux tableaux.
4. Mettez en surbrillance les cellules E6 à E11 dans la feuille de calcul pour entrer la plage. Cette plage est le deuxième tableau que la fonction recherche.
5. Entrez , (une virgule) après la référence de cellule E3 pour terminer la saisie de l’argument Lookup_array de la fonction MATCH.

   

6. Laissez la boîte de dialogue ouverte pour l’étape suivante du didacticiel.

Ajouter l’argument de type MATCH

Le troisième et dernier argument de la fonction MATCH est l’argument Match_type. Cet argument indique à Excel comment faire correspondre Lookup_value avec les valeurs de Lookup_array. Les choix disponibles sont 1, 0 ou -1.

Cet argument est facultatif. S’il est omis, la fonction utilise la valeur par défaut de 1.

• Si Match_type = 1 ou est omis, MATCH trouve la plus grande valeur inférieure ou égale à Lookup_value. Les données Lookup_array doivent être triées par ordre croissant.
• Si Match_type = 0, MATCH trouve la première valeur égale à Lookup_value. Les données Lookup_array peuvent être triées dans n’importe quel ordre.
• Si Match_type = -1, MATCH trouve la plus petite valeur supérieure ou égale à Lookup_value. Les données Lookup_array doivent être triées par ordre décroissant.

Entrez ces étapes après la virgule saisie à l’étape précédente sur la ligne Row_num dans la fonction INDEX:

1. Entrez 0 (un zéro) après la virgule dans la zone de texte Row_num. Ce nombre amène la fonction imbriquée à renvoyer des correspondances exactes aux termes entrés dans les cellules D3 et E3.
2. Entrez ) (un crochet fermant) pour terminer la fonction MATCH.

   

3. Laissez la boîte de dialogue ouverte pour l’étape suivante du didacticiel.

Terminer la fonction INDEX

La fonction MATCH est terminée. Il est temps de passer à la zone de texte Column_num de la boîte de dialogue et d’entrer le dernier argument de la fonction INDEX. Cet argument indique à Excel que le numéro de colonne est compris entre D6 et F11. Cette plage est l’endroit où il trouve les informations renvoyées par la fonction. Dans ce cas, un fournisseur de widgets en titane.

1. Placez le curseur dans la zone de texte Column_num.
2. Entrez 3 (le chiffre trois). Ce nombre indique à la formule de rechercher des données dans la troisième colonne de la plage D6 à F11.

   

3. Laissez la boîte de dialogue ouverte pour l’étape suivante du didacticiel.

Créer la formule matricielle

Avant de fermer la boîte de dialogue, transformez la fonction imbriquée en formule matricielle. Ce tableau permet à la fonction de rechercher plusieurs termes dans la table de données. Dans ce didacticiel, deux termes correspondent: Widgets de la colonne 1 et Titanium de la colonne 2.

Pour créer une formule matricielle dans Excel, appuyez simultanément sur les touches CTRL, MAJ et ENTRÉE. Une fois pressée, la fonction est entourée d’accolades, indiquant que la fonction est maintenant un tableau.

1. Sélectionnez OK pour fermer la boîte de dialogue. Dans Excel pour Mac, sélectionnez Terminé.
2. Sélectionnez la cellule F3 pour afficher la formule, puis placez le curseur à la fin de la formule dans la barre de formule.
3. Pour convertir la formule en tableau, appuyez sur CTRL + MAJ + ENTRÉE.
4. Une erreur #N/A apparaît dans la cellule F3. Il s’agit de la cellule dans laquelle la fonction a été saisie.
5. L’erreur #N/A apparaît dans la cellule F3 car les cellules D3 et E3 sont vides. D3 et E3 sont les cellules où la fonction recherche pour trouver la Lookup_value. Une fois les données ajoutées à ces deux cellules, l’erreur est remplacée par les informations de la base de données.

   

Ajouter les critères de recherche

La dernière étape consiste à ajouter les termes de recherche à la feuille de calcul. Cette étape correspond aux termes Widgets de la colonne 1 et Titanium de la colonne 2.

Si la formule trouve une correspondance pour les deux termes dans les colonnes appropriées de la base de données, elle renvoie la valeur de la troisième colonne.

1. Sélectionnez la cellule D3.
2. Entrez Widgets.
3. Sélectionnez la cellule E3.
4. Tapez Titanium et appuyez sur Entrée.
5. Le nom du fournisseur, Widgets Inc., apparaît dans la cellule F3. Il s’agit du seul fournisseur répertorié qui vend des widgets Titanium.
6. Sélectionnez la cellule F3. La fonction apparaît dans la barre de formule au-dessus de la feuille de calcul. Dans cet exemple, il n’y a qu’un seul fournisseur pour les widgets en titane. S’il y avait eu plusieurs fournisseurs, le fournisseur répertorié en premier dans la base de données est renvoyé par la fonction.
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}