Excel RECHERCHEV et l'opérateur ET : Comment combiner ces puissants outils ?

Comprendre RECHERCHEV et ses limites

La fonction RECHERCHEV (VLOOKUP en anglais) est conçue pour rechercher une valeur dans la première colonne d'une plage de cellules et renvoyer une valeur dans la même ligne, mais dans une colonne spécifiée. Sa syntaxe de base est la suivante :

=RECHERCHEV(valeur_recherchée; table_matrice; no_index_col; [valeur_proche])

• valeur_recherchée: La valeur que vous recherchez.
• table_matrice: La plage de cellules dans laquelle effectuer la recherche. La première colonne de cette plage doit contenir les valeurs de recherche.
• no_index_col: Le numéro de la colonne dans table_matrice qui contient la valeur à renvoyer.
• [valeur_proche]: Facultatif. Indique si vous souhaitez une correspondance exacte (FAUX) ou approximative (VRAI ou omis). Il est fortement recommandé d'utiliser FAUX pour éviter des résultats inattendus.

La limitation de RECHERCHEV avec un seul critère

RECHERCHEV, dans sa forme standard, ne peut rechercher qu'en fonction d'un seul critère. Si vous avez besoin de trouver une valeur basée sur plusieurs conditions simultanées (équivalent à un "ET" logique), vous devrez employer des techniques alternatives ou des fonctions complémentaires.

Combiner RECHERCHEV avec l'opérateur ET : Les solutions

Bien qu'Excel ne possède pas un opérateur "ET" intégré directement utilisable avec RECHERCHEV, plusieurs approches permettent de simuler ce comportement. Voici les plus courantes :

1. Créer une colonne combinée (colonne d'aide)

C'est souvent la méthode la plus simple et la plus performante. Elle consiste à créer une nouvelle colonne dans votre table_matrice qui combine les valeurs des colonnes contenant les critères. Ensuite, vous effectuez votre RECHERCHEV sur cette colonne combinée.

Exemple :

Imaginez une table contenant des informations sur des produits, avec les colonnes "Catégorie" et "Sous-catégorie". Vous voulez trouver le prix d'un produit spécifique en fonction de sa catégorie ET de sa sous-catégorie.

1. Insérez une nouvelle colonne (par exemple, colonne A) avant vos données existantes.

2. Dans la cellule A2, entrez la formule suivante (en supposant que la Catégorie est en colonne B et la Sous-catégorie en colonne C) :

   =B2&"-"&C2

   Cette formule concatène la catégorie, un séparateur (ici, un tiret), et la sous-catégorie. 3. Recopiez cette formule vers le bas pour toutes les lignes de votre tableau. 4. Créez une colonne combinée pour votre critère de recherche. Par exemple, si votre catégorie de recherche est en cellule F1 et votre sous-catégorie en cellule G1, utilisez la formule :

   =F1&"-"&G1

3. Utilisez RECHERCHEV pour rechercher dans la colonne combinée (colonne A dans cet exemple). Supposons que la colonne contenant le prix est la colonne D (maintenant colonne E après l'insertion de la colonne A). La formule RECHERCHEV sera :

   =RECHERCHEV(F1&"-"&G1; A:E; 5; FAUX)

Avantages :

• Simple à comprendre et à implémenter.
• Performante, surtout pour les grands ensembles de données.

Inconvénients :

• Nécessite l'ajout d'une colonne supplémentaire.
• Peut rendre la feuille de calcul moins lisible si la colonne combinée n'est pas masquée.

2. Utiliser la fonction INDEX et la fonction EQUIV

Cette méthode est plus complexe mais ne nécessite pas de colonne d'aide. Elle utilise la fonction INDEX pour renvoyer une valeur à partir d'une plage, et la fonction EQUIV pour trouver la position de la valeur dans cette plage, en appliquant les critères "ET" à l'intérieur de la fonction EQUIV.

Exemple :

Reprenons l'exemple des produits avec les colonnes "Catégorie" (B), "Sous-catégorie" (C) et "Prix" (D). Vous voulez trouver le prix en fonction de la catégorie (F1) et de la sous-catégorie (G1).

La formule sera :

=INDEX(D:D; EQUIV(1; (B:B=F1)*(C:C=G1); 0))

Explication :

• INDEX(D:D; ...): Renvoie une valeur de la colonne D (Prix) à la ligne spécifiée par le résultat de la fonction EQUIV.
• EQUIV(1; (B:B=F1)*(C:C=G1); 0): Recherche la position de la première occurrence de 1 dans le tableau résultant de la multiplication des deux conditions.
  • (B:B=F1): Renvoie un tableau de VRAI et FAUX, où VRAI indique que la catégorie de la ligne correspond à la catégorie de recherche (F1).
  • (C:C=G1): Renvoie un tableau de VRAI et FAUX, où VRAI indique que la sous-catégorie de la ligne correspond à la sous-catégorie de recherche (G1).
  • (B:B=F1)*(C:C=G1): Multiplie les deux tableaux. En Excel, VRAI est traité comme 1 et FAUX comme 0. Donc, le résultat est un tableau de 1 et 0. 1 se produit seulement lorsque les deux conditions sont VRAI (équivalent à un "ET" logique).
  • EQUIV(1; ...; 0): Recherche la première occurrence de 1 dans le tableau résultant, ce qui correspond à la première ligne où les deux conditions sont remplies.

Important : Cette formule est une formule matricielle. Vous devez la valider en appuyant sur Ctrl+Maj+Entrée (Cmd+Maj+Entrée sur Mac). Excel ajoutera automatiquement des accolades {} autour de la formule.

Avantages :

• Ne nécessite pas de colonne d'aide.
• Plus flexible que la méthode de la colonne combinée.

Inconvénients :

• Plus complexe à comprendre et à déboguer.
• Peut être plus lente que la méthode de la colonne combinée pour les très grands ensembles de données.
• Nécessite une validation matricielle.

3. Utiliser la fonction SOMMEPROD (SUMPRODUCT)

La fonction SOMMEPROD peut également être utilisée pour effectuer des recherches multicritères, bien qu'elle soit généralement utilisée pour des calculs de sommes pondérées. Elle est particulièrement utile lorsque vous voulez éviter les formules matricielles.

Exemple :

En reprenant l'exemple des produits, la formule SOMMEPROD pour trouver le prix serait :

=SOMMEPROD((B:B=F1)*(C:C=G1)*(D:D))

Explication :

• (B:B=F1)*(C:C=G1): Comme dans l'exemple précédent, cela crée un tableau de 1 et 0, où 1 indique que les deux conditions (catégorie et sous-catégorie correspondent) sont remplies.
• ...*(D:D): Multiplie ce tableau par la colonne D (Prix). Cela a pour effet de mettre à zéro tous les prix qui ne correspondent pas aux critères.
• SOMMEPROD(...): Somme les éléments du tableau résultant. Comme tous les prix non correspondants sont à zéro, le résultat est le prix du produit qui correspond aux critères.

Avantages :

• Ne nécessite pas de colonne d'aide.
• N'est pas une formule matricielle (pas besoin de Ctrl+Maj+Entrée).
• Relativement simple à comprendre.

Inconvénients :

• Peut être moins performante que la méthode de la colonne combinée ou INDEX/EQUIV pour les très grands ensembles de données.
• Si plusieurs lignes correspondent aux critères, elle renverra la somme des prix, ce qui n'est probablement pas ce que vous voulez.

Bonnes pratiques et astuces pour RECHERCHEV et l'opérateur ET

• Utilisez toujours FAUX (ou 0) pour le paramètre valeur_proche de RECHERCHEV, sauf si vous avez une raison spécifique d'utiliser une correspondance approximative. Une correspondance approximative peut donner des résultats inattendus si vos données ne sont pas triées correctement.
• Nommez vos plages de cellules. Cela rend vos formules plus lisibles et plus faciles à maintenir. Par exemple, au lieu d'utiliser A:E, vous pouvez nommer cette plage TableProduits.

• Utilisez la fonction SIERREUR (IFERROR) pour gérer les erreurs. Si RECHERCHEV ne trouve pas de correspondance, elle renvoie l'erreur #N/A. Vous pouvez utiliser SIERREUR pour afficher un message plus convivial, comme "Produit non trouvé". Exemple :

  =SIERREUR(RECHERCHEV(F1&"-"&G1; A:E; 5; FAUX); "Produit non trouvé") * Pensez à utiliser RECHERCHEX (XLOOKUP) si vous avez une version récente d'Excel. RECHERCHEX est une version plus puissante et flexible de RECHERCHEV qui résout plusieurs de ses limitations, y compris la nécessité d'avoir la colonne de recherche à gauche et la gestion plus simple des erreurs. * Optimisez vos données. Assurez-vous que vos données sont propres et cohérentes. Les erreurs de saisie ou les incohérences dans les données peuvent empêcher RECHERCHEV de trouver une correspondance. * Testez vos formules. Vérifiez toujours que vos formules renvoient les résultats attendus en utilisant des exemples de données connues.

Erreurs courantes et comment les éviter

• Erreur #N/A : Indique que RECHERCHEV n'a pas trouvé de correspondance. Vérifiez que la valeur_recherchée existe dans la première colonne de la table_matrice, et que vous utilisez FAUX pour une correspondance exacte.
• Erreur #REF! : Indique que le no_index_col est supérieur au nombre de colonnes dans la table_matrice. Vérifiez que le numéro de colonne est correct.
• Résultats incorrects avec une correspondance approximative : Si vous utilisez VRAI pour le paramètre valeur_proche, assurez-vous que la première colonne de la table_matrice est triée par ordre croissant.
• Oublier de valider une formule matricielle (INDEX/EQUIV) : Si vous utilisez la méthode INDEX/EQUIV, n'oubliez pas de valider la formule en appuyant sur Ctrl+Maj+Entrée (Cmd+Maj+Entrée sur Mac).

Conclusion

Combiner RECHERCHEV avec une logique "ET" est essentiel pour effectuer des recherches multicritères efficaces dans Excel. Bien que RECHERCHEV ne prenne pas en charge directement l'opérateur "ET", les méthodes présentées dans cet article (colonne combinée, INDEX/EQUIV, SOMMEPROD) vous permettent de surmonter cette limitation. En choisissant la méthode la plus appropriée à votre situation et en suivant les bonnes pratiques, vous pouvez exploiter pleinement la puissance de RECHERCHEV pour extraire des informations précieuses de vos données Excel.