Comment rechercher plusieurs valeurs dans Excel : étape par étape

Lorsque vous travaillez avec de grands ensembles de données dans Microsoft Excel, il peut être difficile de trouver plusieurs valeurs correspondant à plusieurs conditions. Certaines fonctions intégrées, par exemple VLOOKUP, ont été conçues à l'origine pour fonctionner avec des valeurs uniques.

Plusieurs fonctions Excel peuvent être combinées pour rechercher plusieurs valeurs. Ils incluent les fonctions VLOOKUP, INDEX, MATCH et IF. Les versions actuelles d'Excel peuvent utiliser des tableaux dynamiques tandis que les anciennes versions utilisent des formules matricielles.

Cet article vous montrera exactement comment utiliser ces fonctions dans des formules qui trouvent plusieurs valeurs dans vos données.

Allons-y!

Table des matières

• Comment utiliser VLOOKUP avec plusieurs valeurs
  • Que sont les formules matricielles ?
  • Exemple RECHERCHEV
  • Dernières versions d'Excel
  • Versions plus anciennes
  • Correspondance exacte vs correspondance approximative
  • En savoir plus sur RECHERCHEV
• Comment utiliser INDEX et MATCH pour rechercher plusieurs valeurs
  • Comment utiliser INDEX et MATCH ensemble dans Excel 365
  • Utilisation d'anciennes versions d'Excel
• Comment utiliser la fonction SI pour rechercher plusieurs valeurs
  • Versions actuelles d'Excel
  • Anciennes versions d'Excel
• Comment utiliser SUMPRODUCT pour plusieurs critères
• 3 erreurs courantes avec les fonctions de recherche
  • 1. Erreurs #N/A
  • 2. #REF ! les erreurs
  • 3. Erreurs circulaires
• Comment utiliser IFERROR avec les fonctions de recherche
• 7 techniques de recherche avancées
  • 1. Recherche de position relative
  • 2. PETITE Fonction
  • 3. Fonction LIGNE
  • 4. Fonction FILTRE
  • 5. Colonnes d'assistance
  • 6. Tableaux dynamiques
  • 7. Requête de puissance
• Dernières pensées

Comment utiliser VLOOKUP avec plusieurs valeurs

La fonction VLOOKUP est souvent utilisée pour trouver des valeurs uniques dans une plage de données. Cependant, vous pouvez également rechercher plusieurs correspondances dans Excel avec cette formule de recherche.

Par défaut, il ne renverra que la première valeur correspondante trouvée. Cependant, vous pouvez modifier la fonction pour qu'elle renvoie plusieurs valeurs à l'aide d'une formule matricielle.

Que sont les formules matricielles ?

Une formule matricielle est une formule qui peut effectuer des calculs sur des tableaux de données. C'est ce qu'on appelle une formule matricielle car elle peut renvoyer un tableau de résultats, plutôt qu'une valeur unique.

Il existe plusieurs étapes simples lors de la création d'une formule matricielle :

1. Sélectionnez une plage de cellules à rechercher.

2. Entrez la formule dans la barre de formule.

3. Appuyez sur Ctrl + Maj + Entrée pour le terminer.

La syntaxe d'une formule matricielle est similaire à celle d'une formule normale, mais elle inclut des accolades {} autour de la formule. Les accolades indiquent que la formule est une formule matricielle et qu'elle renverra un tableau de valeurs.

Les exemples de cet article vous montreront comment utiliser correctement les formules matricielles.

Exemple RECHERCHEV

Notre exemple comporte cinq éléments dans la colonne A d'une feuille de calcul :

• Pomme

• Banane

• Pain

• Carotte

• Cerise

La tâche consiste à vérifier si trois fruits spécifiques figurent dans cette liste : pomme, banane et cerise.

Dernières versions d'Excel

La syntaxe VLOOKUP dépend de la version d'Excel que vous utilisez.

Les versions les plus récentes ( Excel 365 ou Excel 2021 ) supportent les tableaux dynamiques. Cette fonctionnalité permet aux formules de renvoyer plusieurs résultats qui « se répandent » dans les cellules adjacentes.

Voici la syntaxe (le signe égal commence la formule) :

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value : la valeur que vous souhaitez rechercher.

• table_array : toute la table de données que vous souhaitez rechercher.

• col_index_num : numéro de colonne de table dans table_array qui contient les données que vous souhaitez renvoyer.

• range_lookup : facultatif. Spécifie si vous voulez une correspondance exacte ou une correspondance approximative.

Notre exemple spécifique utilise cette formule :

= RECHERCHEV({"pomme", "banane", "cerise"},A1:A5,1,FAUX)

Lorsque la formule est entrée dans la cellule B1, les résultats se répandent dans les cellules C1 et D1. Cette image montre l'exemple en action :

Versions plus anciennes

Si vous utilisez une ancienne version d'Excel qui ne prend pas en charge les tableaux dynamiques (par exemple, Excel 2019 ou version antérieure), vous devrez utiliser une approche légèrement différente avec une formule matricielle.

Suivez ces étapes:

1. Cliquez sur la cellule où vous souhaitez afficher les résultats pour le premier élément (par exemple, la colonne B).

2. Tapez la formule suivante sans appuyer sur Entrée : =VLOOKUP({"apple", "banana", "cherry"}, A1: A5, 1, FALSE)

3. Appuyez sur Ctrl + Maj + Entrée pour transformer cela en une formule matricielle.

4. Copiez la cellule B1 et collez-la dans la cellule ci-dessous (ou utilisez les poignées de recopie).

Lorsque vous utilisez Ctrl + Maj + Entrée, Excel ajoute des accolades autour de la formule. Cela indique qu'il s'agit d'une formule matricielle.

Correspondance exacte vs correspondance approximative

Par défaut, la fonction VLOOKUP utilise une correspondance approximative. Cela signifie qu'il renverra la correspondance la plus proche qu'il peut trouver, même si les valeurs des cellules ne correspondent pas exactement.

Si vous souhaitez effectuer une correspondance exacte, vous pouvez définir l'argument range_lookup sur FALSE.

Gardez à l'esprit que les correspondances approximatives fonctionnent mieux avec des valeurs numériques ordonnées. Ce n'est généralement pas approprié lorsque la valeur de la cellule est du texte.

En savoir plus sur RECHERCHEV

Si vous souhaitez en savoir plus sur cette fonction polyvalente, consultez ces articles :

• comment comparer deux colonnes dans Excel avec VLOOKUP

• comment trouver des divergences avec VLOOKUP

Maintenant que vous êtes configuré avec la fonction VLOOKUP, examinons deux autres fonctions qui peuvent faire ce qu'elle fait d'une manière différente : INDEX et MATCH.

Comment utiliser INDEX et MATCH pour rechercher plusieurs valeurs

Vous pouvez combiner les fonctions INDEX et MATCH pour rechercher plusieurs valeurs dans plusieurs lignes.

La fonction INDEX dans Excel renvoie une valeur ou une référence à une cellule dans une plage spécifiée.

=INDEX(tableau, num_ligne, [num_colonne])

• array : la plage de cellules dans laquelle rechercher la valeur.

• row_num : numéro de ligne dans le tableau à partir duquel renvoyer une valeur.

• column_num : (facultatif) numéro de colonne dans le tableau à partir duquel renvoyer une valeur. Si omis, la fonction renvoie la ligne entière.

La fonction MATCH dans Excel renvoie la position d'une valeur dans une plage spécifiée.

=MATCH(lookup_value, lookup_array, [match_type])

• lookup_value : la valeur à rechercher dans le lookup_array.

• lookup_array : la plage de cellules à rechercher pour la lookup_value.

• match_type : (facultatif) type de correspondance à effectuer. Si omis, la fonction effectue une correspondance exacte.

Comment utiliser INDEX et MATCH ensemble dans Excel 365

Pour utiliser INDEX et MATCH ensemble pour rechercher plusieurs valeurs dans Excel, vous devez utiliser une formule matricielle.

En travaillant avec les exemples de données précédents, voici la formule dans Excel 365 :

=INDEX(A1:A5, CORRESPONDANCE({“pomme”,”banane”,,”cerise”}, A1:A5, 0))

L'exemple ci-dessus se décompose comme suit :

• INDEX : cela renvoie la valeur d'une cellule dans une plage spécifiée en fonction d'un numéro de ligne et de colonne donné. Dans ce cas, il renverra la valeur de la plage A1:A5.

• A1:A5 : Il s'agit de la plage de table définie dans laquelle vous recherchez la valeur et à partir de laquelle le résultat sera renvoyé.

• MATCH : cela recherche un élément spécifié dans une plage de cellules et renvoie la position relative de cet élément dans la plage.

• {"pomme", "banane", "cerise"} : il s'agit de la constante de tableau contenant les valeurs que vous souhaitez rechercher.

• A1:A5 : il s'agit de la plage dans laquelle MATCH recherchera les valeurs de la constante matricielle.

• 0 : il s'agit du type de correspondance pour la fonction MATCH. Dans ce cas, c'est 0, ce qui signifie que vous recherchez une correspondance exacte au lieu d'une correspondance proche.

 Cette image montre la formule en action :

Utilisation d'anciennes versions d'Excel

Si vous utilisez un ancien fichier Excel qui ne prend pas en charge les tableaux dynamiques (par exemple, Excel 2019 ou version antérieure), vous devrez utiliser une approche différente.

Étant donné que les anciennes versions ne prennent pas en charge les formules "débordant" dans les cellules adjacentes, vous devrez diviser l'utilisation en trois formules distinctes.

Suivez ces étapes:

1. Cliquez sur la cellule où vous voulez le résultat pour le premier élément (par exemple, la cellule B1)

2. Tapez la formule ci-dessous :

3. =INDEX(A1:A5, EQUIV("pomme", A1:A5, 0))

4. Appuyez sur Entrée pour exécuter la formule.

5. Tapez cette formule dans la cellule B2 : =INDEX(A1:A5, MATCH("banana", A1:A5, 0))

6. Tapez cette formule dans la cellule B3 : =INDEX(A1:A5, MATCH("cherry", A1:A5, 0))

Cette image montre la référence de cellule B3 :

Les fonctions INDEX et MATCH ne sont pas les seules à pouvoir vous permettre de trouver plusieurs valeurs. Dans la section suivante, nous verrons comment vous pouvez utiliser la fonction SI comme alternative.

Comment utiliser la fonction SI pour rechercher plusieurs valeurs

Une autre façon de rechercher plusieurs valeurs de cellule en fonction de certains critères consiste à utiliser la fonction SI avec d'autres fonctions.

La fonction SI vous permet de tester plusieurs conditions et de renvoyer des résultats différents en fonction du résultat de ces tests.

Par exemple, supposons que vous disposiez d'un tableau de données de ventes avec des colonnes pour Product et Sales. Vous souhaitez rechercher et totaliser le montant des ventes pour deux des trois produits.

Versions actuelles d'Excel

Pour trouver la somme de la colonne Ventes où le produit est "Pomme" ou "Banane" à l'aide de la fonction SI, vous pouvez utiliser une formule matricielle avec les fonctions SI, SOMME et OU.

En supposant que vos données commencent dans la cellule A1, utilisez la formule suivante :

=SOMME(SI((A2:A4=”Pomme”)+(A2:A4=”Banane”), B2:B4, 0))

La section (A2:A4=”Pomme”)+(A2:A4=”Banane”) crée un tableau qui a une valeur de 1 si la cellule de la plage A2:A4 contient “Pomme” ou “Banane”, et 0 sinon .

L'instruction IF vérifie chaque élément de l'argument du tableau. Si la valeur est 1 (c'est-à-dire que le produit est soit "Pomme" soit "Banane"), il prend la valeur correspondante dans la colonne Ventes (plage B2 : B4) ; sinon, il faut 0.

La fonction SUM additionne les valeurs de la fonction IF, en additionnant efficacement les valeurs de vente pour « Pomme » et « Banane ».

Cette image montre la formule en action sur la plage de recherche :

Anciennes versions d'Excel

Dans Excel 2019 ou version antérieure, vous devez utiliser une formule matricielle. Suivez ces étapes:

1. Tapez la formule mais n'appuyez pas sur Entrée.

2. Appuyez sur Ctrl + Maj + Entrée pour en faire une formule matricielle.

Excel ajoutera des accolades {} autour de la formule, indiquant qu'il s'agit d'une formule matricielle.

Ensuite, nous examinons comment vous pouvez utiliser SUMPRODUCT pour rechercher plusieurs valeurs en fonction de vos critères. Allons-y!

Comment utiliser SUMPRODUCT pour plusieurs critères

La fonction SUMPRODUCT vous permet également de rechercher plusieurs valeurs en fonction de plusieurs critères.

Comme il ne nécessite pas l'utilisation d'une formule matricielle, la syntaxe est la même quelle que soit la version d'Excel.

En utilisant les mêmes données que dans l'exemple précédent, la formule ressemble à ceci :

=SOMMEPROD((A2:A4=”Pomme”)+(A2:A4=”Banane”), B2:B4)

La section (A2:A4=”Pomme”)+(A2:A4=”Banane”) crée un tableau qui a une valeur de 1 si la cellule de la plage A2:A4 contient “Pomme” ou “Banane”, et 0 sinon .

La fonction SOMMEPROD multiplie les éléments du tableau avec les éléments correspondants dans la colonne Ventes (plage B2:B4). Il additionne ensuite les valeurs résultantes, en additionnant efficacement les valeurs des ventes pour "Apple" et "Banana".

La formule ci-dessous le montre en action :

Les fonctions Excel sont étonnantes lorsqu'elles fonctionnent comme prévu, mais vous pouvez parfois rencontrer des erreurs. Dans la section suivante, nous couvrons certaines des erreurs courantes et comment vous pouvez les traiter.

3 erreurs courantes avec les fonctions de recherche

Les fonctions de recherche peuvent parfois renvoyer des erreurs qui peuvent être frustrantes et longues à résoudre. Les trois erreurs les plus courantes que vous rencontrerez sont :

1. #N/A Erreurs

2. #REF! les erreurs

3. Erreurs circulaires

1. Erreurs #N/A

L'erreur #N/A se produit lorsque la valeur de recherche est introuvable dans le tableau de recherche.

Cette erreur peut se produire pour plusieurs raisons, notamment :

• la valeur de recherche est mal orthographiée ou incorrecte.

• le tableau de recherche n'est pas trié par ordre croissant.

• la valeur de recherche n'est pas dans l'ensemble de données.

Lorsque la valeur de recherche ne se trouve pas dans l'ensemble de données, il s'agit d'informations utiles. Cependant, les utilisateurs Excel inexpérimentés peuvent penser que #N/A signifie que quelque chose ne va pas avec la formule. La section suivante montre comment rendre cela plus convivial.

2. #REF ! les erreurs

Le #REF ! L'erreur se produit lorsque le tableau de recherche ou le tableau de retour est supprimé ou déplacé.

Cette erreur peut être corrigée en mettant à jour les références de cellule dans la fonction de recherche.

3. Erreurs circulaires

Lorsque vous combinez des fonctions dans des formules complexes, Excel peut vous indiquer que vous avez une référence circulaire.

Vous les trouverez plus faciles à étudier en utilisant notre guide pour trouver des références circulaires dans Excel .

Comment utiliser IFERROR avec les fonctions de recherche

La fonction SIERREUR est un outil utile pour gérer les erreurs dans les fonctions de recherche. Il vous permet de spécifier une valeur ou une formule à renvoyer si la fonction de recherche renvoie une erreur.

La syntaxe de la fonction SIERREUR est la suivante :

=IFERREUR(valeur, valeur_si_erreur)

· valeur : valeur ou formule que vous souhaitez évaluer.

· Value_if_error : la valeur ou la formule à renvoyer si le premier argument renvoie une erreur.

Par exemple, supposons que vous ayez une fonction VLOOKUP qui recherche plusieurs valeurs dans une table. Dans l'image ci-dessous, l'une des valeurs n'existe pas dans la plage de données recherchée.

Comme vous pouvez le constater, l'erreur #N/A s'affiche, ce qui peut prêter à confusion pour les utilisateurs Excel inexpérimentés.

Au lieu de cela, vous pouvez utiliser SIERREUR pour afficher une cellule vide ou un message indiquant « Non trouvé » avec cette syntaxe :

=SIFERROR(VLOOKUP(lookup_value, table_array, column_index, FALSE), "Non trouvé")

Dans cet exemple, si la fonction RECHERCHEV renvoie une erreur, la fonction SIERREUR renverra le message "Non trouvé" à la place.

Cette image montre la formule en action. La colonne B a la valeur manquante, tandis que la colonne C et la colonne D ont trouvé des correspondances.

Nous avons couvert beaucoup de terrain jusqu'à présent, et vous êtes enfin prêt à apprendre des techniques plus avancées pour les recherches, ce qui est le sujet de la section suivante.

7 techniques de recherche avancées

La recherche de plusieurs valeurs dans Excel peut être une tâche difficile, en particulier lorsqu'il s'agit de grands ensembles de données. Vous pouvez rencontrer des problèmes de performances avec un traitement lent.

Il existe sept techniques de recherche avancées que vous pouvez utiliser pour rendre le processus plus simple et plus efficace.

• Recherche de position relative

• PETITE fonction

• Fonction LIGNE

• Fonction FILTRE

• Colonnes d'assistance

• Tableaux dynamiques

• Requête de puissance

1. Recherche de position relative

L'un des moyens les plus simples de rechercher plusieurs valeurs dans Excel consiste à utiliser la recherche de position relative. Cela implique de spécifier les décalages de ligne et de colonne à partir d'une cellule donnée pour localiser la valeur souhaitée.

Par exemple, si vous avez un tableau de données et que vous souhaitez rechercher une valeur située deux lignes plus bas et trois colonnes à droite d'une cellule donnée, vous pouvez utiliser la formule suivante :

=OFFSET(cellule, 2, 3)

2. PETITE Fonction

Une autre technique utile pour la recherche de valeurs multiples consiste à utiliser la fonction SMALL. Cette fonction renvoie la nième plus petite valeur d'une plage de cellules.

En combinant cette fonction avec d'autres fonctions de recherche telles que INDEX et MATCH, vous pouvez rechercher plusieurs valeurs en fonction de critères spécifiques.

Par exemple, la formule suivante recherche la deuxième plus petite valeur dans une plage de cellules remplissant une certaine condition :

=INDEX(données, CORRESPONDANCE(PETIT(SI(critère, intervalle), 2), intervalle, 0))

3. Fonction LIGNE

La fonction ROW peut également être utilisée pour plusieurs recherches de valeurs dans Excel. Cette fonction renvoie le numéro de ligne d'une cellule donnée, qui peut être utilisé pour référencer des cellules dans un tableau de données.

Par exemple, la formule suivante recherche une valeur dans une table en fonction d'un identifiant unique :

=INDEX(données, MATCH(identifiant_unique, données[identifiant_unique], 0), numéro_index_colonne)

4. Fonction FILTRE

La fonction FILTRE n'est pas disponible dans les anciennes versions d'Excel.

Dans Excel 365, vous pouvez l'utiliser pour filtrer une plage de cellules en fonction de certains critères et renvoyer uniquement les valeurs qui répondent à ces critères. Voici la syntaxe et trois arguments :

=FILTRE(tableau, inclure, [if_empty])

• tableau : les données spécifiques que vous souhaitez filtrer.

• include : L'argument include est le critère ou les conditions que vous souhaitez appliquer au tableau.

• [if_empty] (facultatif) : la valeur à renvoyer si aucune ligne ou colonne ne répond aux critères spécifiés dans l'argument d'inclusion.

Par exemple, la formule suivante fonctionne sur les exemples de données pour trouver des correspondances pour deux des trois éléments de la première colonne et additionner leurs valeurs correspondantes dans la deuxième colonne.

=SOMME(FILTRE(B2:B4, (A2:A4=”Pomme”)+(A2:A4=”Banane”)))

Cette image montre combien de lignes correspondent et la somme des différentes valeurs :

5. Colonnes d'assistance

Vous pouvez utiliser une colonne d'assistance pour joindre plusieurs champs dans une fonction telle que VLOOKUP.

Supposons que vous travailliez avec les noms et prénoms dans une feuille séparée. Vous pouvez les concaténer dans une colonne d'assistance référencée dans la formule finale.

6. Tableaux dynamiques

Comme vous l'avez appris dans les exemples précédents, les utilisateurs de Microsoft 365 peuvent tirer parti des tableaux dynamiques pour la recherche de plusieurs valeurs dans Excel.

Les tableaux dynamiques vous permettent de renvoyer plusieurs valeurs à partir d'une seule formule, ce qui facilite la recherche de grandes quantités de données.

7. Requête de puissance

Power Query est un outil puissant dans Excel qui peut être utilisé pour renvoyer des valeurs basées sur plusieurs critères.

Par exemple, cette vidéo trouve des données désordonnées dans une feuille de calcul et les nettoie.

Dernières pensées

Cela conclut notre plongée profonde dans l'art de rechercher plusieurs valeurs dans Excel ! Une fois que vous aurez maîtrisé les formules VLOOKUP, INDEX, MATCH et tableau, vous vous retrouverez à parcourir des ensembles de données complexes comme un couteau chaud dans du beurre.

La clé est de comprendre la syntaxe et la logique derrière chaque formule. Gardez à l'esprit que ces formules peuvent être complexes, il est donc important de prendre votre temps et de tester soigneusement vos formules avant de vous y fier pour une analyse de données importante.

Excel est un outil puissant, mais sa qualité dépend de l'utilisateur. Alors continuez à perfectionner ces compétences, continuez à expérimenter et vous serez bientôt le maître des recherches multi-valeurs. Jusqu'à la prochaine fois, continuez à calculer ces chiffres et faites en sorte qu'Excel fasse le travail acharné pour vous !