Comment utiliser RECHERCHEV avec plusieurs critères dans Excel

Regarder la vidéo - Comment utiliser la fonction RECHERCHEV avec plusieurs critères

La fonction Excel VLOOKUP, dans sa forme de base, peut rechercher une valeur de recherche et renvoyer la valeur correspondante à partir de la ligne spécifiée.

Mais souvent, il est nécessaire d'utiliser Excel VLOOKUP avec plusieurs critères.

Comment utiliser RECHERCHEV avec plusieurs critères

Supposons que vous ayez des données avec le nom des étudiants, le type d'examen et le score en mathématiques (comme indiqué ci-dessous) :

L'utilisation de la fonction RECHERCHEV pour obtenir le score en mathématiques de chaque étudiant pour les niveaux d'examen respectifs pourrait être un défi.

On peut affirmer qu'une meilleure option serait de restructurer l'ensemble de données ou d'utiliser un tableau croisé dynamique. Si cela fonctionne pour vous, rien de tel. Mais dans de nombreux cas, vous êtes coincé avec les données dont vous disposez et le tableau croisé dynamique peut ne pas être une option.

Dans de tels cas, ce tutoriel est pour vous.

Il existe maintenant deux manières d'obtenir la valeur de recherche en utilisant RECHERCHEV avec plusieurs critères.

• Utilisation d'une colonne d'assistance.
• Utilisation de la fonction CHOISIR.

RECHERCHEV avec plusieurs critères - Utilisation d'une colonne d'assistance

Je suis un fan des colonnes d'aide dans Excel.

Je trouve deux avantages importants à utiliser des colonnes d'aide par rapport aux formules matricielles :

• Cela permet de comprendre facilement ce qui se passe dans la feuille de travail.
• Cela le rend plus rapide par rapport aux fonctions de tableau (remarquable dans les grands ensembles de données).

Maintenant, ne vous méprenez pas. Je ne suis pas contre les formules matricielles. J'aime les choses incroyables qui peuvent être faites avec les formules matricielles. C'est juste que je les garde pour des occasions spéciales où toutes les autres options ne sont d'aucune utilité.

Pour en revenir à la question en question, la colonne d'aide est nécessaire pour créer un qualificatif unique. Ce qualificatif unique peut ensuite être utilisé pour rechercher la valeur correcte. Par exemple, il y a trois Matt dans les données, mais il n'y a qu'une seule combinaison de Matt et Unit Test ou Matt et Mid-Term.

Voici les étapes :

• Insérez une colonne auxiliaire entre les colonnes B et C.
• Utilisez la formule suivante dans la colonne d'aide : = A2&”|”&B2
  • Cela créerait des qualificatifs uniques pour chaque instance, comme indiqué ci-dessous.
• Utilisez la formule suivante dans G3 =RECHERCHEV($F3&”|”&G$2,$C$2:$D$19,2,0)
• Copie pour toutes les cellules.

Comment cela marche-t-il?

Nous créons des qualificatifs uniques pour chaque instance d'un nom et de l'examen. Dans la fonction RECHERCHEV utilisée ici, la valeur de recherche a été modifiée en $F3&”|”&G$2 afin que les deux critères de recherche soient combinés et utilisés comme une seule valeur de recherche. Par exemple, la valeur de recherche de la fonction RECHERCHEV dans G2 est Matt|Test unitaire. Cette valeur de recherche est maintenant utilisée pour obtenir le score de C2:D19.

Précisions :

Il y a quelques questions qui sont susceptibles de vous venir à l'esprit, alors j'ai pensé que je vais essayer d'y répondre ici :

• Pourquoi ai-je utilisé | symbole en joignant les deux critères ? - Dans certaines conditions exceptionnellement rares (mais possibles), vous pouvez avoir deux critères différents mais qui finissent par donner le même résultat lorsqu'ils sont combinés. Voici un exemple très simple (pardonnez-moi pour mon manque de créativité ici) :

Notez que si A2 et A3 sont différents et que B2 et B3 sont différents, les combinaisons finissent par être les mêmes. Mais si vous utilisez un séparateur, alors même la combinaison serait différente (D2 et D3).

• Pourquoi ai-je inséré la colonne d'aide entre les colonnes B et C et non à l'extrême gauche ? - Il n'y a aucun mal à insérer la colonne d'aide à l'extrême gauche. En fait, si vous ne voulez pas tempérer avec les données d'origine, cela devrait être la voie à suivre. Je l'ai fait car cela me fait utiliser moins de cellules dans la fonction RECHERCHEV. Au lieu d'avoir 4 colonnes dans le tableau, je pouvais me débrouiller avec seulement 2 colonnes. Mais c'est juste moi.

Maintenant, il n'y a pas de taille unique qui convient à tous. Certaines personnes peuvent préférer ne pas utiliser de colonne d'aide lors de l'utilisation de RECHERCHEV avec plusieurs critères.

Voici donc la méthode de la colonne non auxiliaire pour vous.

Télécharger le fichier exemple

RECHERCHEV avec plusieurs critères - Utilisation de la fonction CHOOSE

L'utilisation de formules matricielles au lieu de colonnes d'aide vous permet d'économiser de l'espace sur la feuille de calcul et les performances peuvent être tout aussi bonnes si elles sont utilisées moins de fois dans un classeur.

En considérant le même ensemble de données que celui utilisé ci-dessus, voici la formule qui vous donnera le résultat :

=RECHERCHEV($E3&”|”&F$2,CHOISIR({1,2},$A$2:$A$19&”|”&$B$2:$B$19,$C$2:$C$19),2, 0)

Puisqu'il s'agit d'une formule matricielle, utilisez-la avec Ctrl + Maj + Entrée, au lieu de simplement Entrée.

Comment cela marche-t-il?

La formule utilise également le concept de colonne d'aide. La différence est qu'au lieu de placer la colonne d'assistance dans la feuille de calcul, considérez-la comme des données d'assistance virtuelle faisant partie de la formule.

Laissez-moi vous montrer ce que j'entends par données d'aide virtuelle.

Dans l'illustration ci-dessus, lorsque je sélectionne la partie CHOOSE de la formule et que j'appuie sur F9, cela montre le résultat que la formule CHOOSE donnerait.

Le résultat est {“Matt|Test unitaire”, 91;”Bob|Test unitaire”, 52;… }

C'est un tableau où une virgule représente la cellule suivante dans la même ligne et le point-virgule représente que les données suivantes sont dans la colonne suivante. Par conséquent, cette formule crée 2 colonnes de données - une colonne a l'identifiant unique et l'autre a le score.

Désormais, lorsque vous utilisez la fonction RECHERCHEV, elle recherche simplement la valeur dans la première colonne (de ces données virtuelles à 2 colonnes) et renvoie le score correspondant.

Télécharger le fichier exemple

Vous pouvez également utiliser d'autres formules pour effectuer une recherche avec plusieurs critères (tels que INDEX/MATCH ou SUMPRODUCT).

Connaissez-vous une autre façon de procéder ? Si oui, partagez-le avec moi dans la section commentaires.

Vous aimerez peut-être aussi les didacticiels LOOKUP suivants :

• RECHERCHEV vs. INDEX/MATCH
• Obtenez plusieurs valeurs de recherche sans répétition dans une seule cellule.
• Comment rendre VLOOKUP sensible à la casse.
• Utilisez IFERROR avec RECHERCHEV pour vous débarrasser des erreurs #N/A.