Excel INDEX & EQUIV : Maîtrisez la formule qui remplace RECHERCHEV

Pourquoi Utiliser INDEX & EQUIV au Lieu de RECHERCHEV dans Excel ?

RECHERCHEV est souvent la première fonction de recherche qu'apprennent les utilisateurs d'Excel. Cependant, elle présente plusieurs inconvénients :

• Dépendance de la position de la colonne : RECHERCHEV exige que la colonne de recherche soit la plus à gauche de la plage. Si vous insérez ou supprimez des colonnes, votre formule peut se casser.
• Recherche uniquement vers la droite : RECHERCHEV ne peut rechercher des valeurs que dans les colonnes situées à droite de la colonne de recherche.
• Moins flexible : RECHERCHEV est moins adaptable aux changements de structure de vos données.

INDEX et EQUIV, en revanche, offrent une solution plus puissante et flexible.

Comprendre les Fonctions INDEX et EQUIV

Avant de combiner ces deux fonctions, il est crucial de comprendre comment chacune fonctionne individuellement.

La Fonction INDEX

La fonction INDEX renvoie la valeur d'une cellule dans une plage, en fonction de son numéro de ligne et de colonne. Elle a deux formes :

• Forme matricielle : INDEX(matrice, no_ligne, [no_colonne])
• Forme de référence : INDEX(référence, no_ligne, [no_colonne], [no_zone])

Dans la forme matricielle (la plus courante), matrice est la plage de cellules à partir de laquelle vous voulez extraire une valeur, no_ligne est le numéro de la ligne dans cette plage et no_colonne est le numéro de la colonne. Si la matrice est une seule colonne ou une seule ligne, l'argument no_colonne (ou no_ligne) peut être omis.

Exemple INDEX :

Supposons que vous ayez une plage de cellules A1:C5 contenant des données. La formule =INDEX(A1:C5, 2, 3) renverra la valeur de la cellule située à la deuxième ligne et à la troisième colonne de cette plage (donc la cellule C2).

La Fonction EQUIV

La fonction EQUIV recherche une valeur spécifiée dans une plage de cellules et renvoie la position relative de cette valeur dans la plage. Sa syntaxe est la suivante :

EQUIV(valeur_recherchée, plage_recherche, [type])

• valeur_recherchée est la valeur que vous voulez trouver.
• plage_recherche est la plage de cellules dans laquelle vous voulez rechercher la valeur.
• type (optionnel) spécifie le type de correspondance :
  • 0 : Trouve la première valeur exactement égale à valeur_recherchée.
  • 1 : Trouve la plus grande valeur inférieure ou égale à valeur_recherchée. La plage_recherche doit être triée en ordre croissant.
  • -1 : Trouve la plus petite valeur supérieure ou égale à valeur_recherchée. La plage_recherche doit être triée en ordre décroissant.

Exemple EQUIV :

Si les cellules A1:A5 contiennent les valeurs {10, 20, 30, 40, 50}, la formule =EQUIV(30, A1:A5, 0) renverra 3, car 30 est la troisième valeur dans la plage A1:A5.

Combiner INDEX et EQUIV : La Formule Magique

La puissance de INDEX et EQUIV réside dans leur combinaison. EQUIV trouve la position d'une valeur, et INDEX utilise cette position pour renvoyer la valeur correspondante dans une autre colonne. La syntaxe générale est :

=INDEX(plage_résultat, EQUIV(valeur_recherchée, plage_recherche, 0))

Où :

• plage_résultat est la colonne ou la plage qui contient la valeur que vous voulez renvoyer.
• valeur_recherchée est la valeur que vous voulez rechercher.
• plage_recherche est la colonne ou la plage dans laquelle vous voulez rechercher la valeur_recherchée.

Exemple Pratique :

Imaginez une feuille Excel avec une liste de produits dans la colonne A (A1:A10) et leurs prix correspondants dans la colonne B (B1:B10). Vous voulez trouver le prix d'un produit spécifique, par exemple, "Produit C".

1. Valeur recherchée : "Produit C"
2. Plage de recherche : A1:A10 (la colonne des produits)
3. Plage de résultat : B1:B10 (la colonne des prix)

La formule serait :

=INDEX(B1:B10, EQUIV("Produit C", A1:A10, 0))

Cette formule recherche "Produit C" dans la colonne A (A1:A10) et renvoie sa position (par exemple, 3). Ensuite, INDEX utilise cette position (3) pour renvoyer la valeur de la troisième cellule dans la colonne B (B1:B10), qui est le prix du "Produit C".

Avantages de INDEX & EQUIV par Rapport à RECHERCHEV

• Flexibilité de la colonne de recherche : La colonne de recherche (plage_recherche dans EQUIV) n'a pas besoin d'être la première colonne de la plage. Vous pouvez rechercher dans n'importe quelle colonne.
• Recherche à gauche : Vous pouvez rechercher des valeurs dans des colonnes situées à gauche de la colonne de recherche, ce que RECHERCHEV ne peut pas faire.
• Moins sensible aux insertions et suppressions de colonnes : Tant que les plages de recherche et de résultat restent correctes, la formule continuera de fonctionner même si vous modifiez la structure de votre feuille de calcul.

Exemples Avancés et Astuces

Recherche sur Plusieurs Colonnes

Vous pouvez utiliser INDEX & EQUIV pour effectuer des recherches basées sur plusieurs critères. Cela implique de combiner plusieurs colonnes en une seule colonne de recherche en utilisant un séparateur.

Exemple :

Supposons que vous ayez une table avec les colonnes "Nom", "Prénom" et "Adresse e-mail". Vous voulez trouver l'adresse e-mail d'une personne en fonction de son nom et prénom.

1. Créez une colonne combinée : Dans une nouvelle colonne (par exemple, la colonne D), concaténez le nom et le prénom avec un séparateur (par exemple, un point-virgule) : =A1&";"&B1
2. Utilisez INDEX & EQUIV : La formule pour trouver l'adresse e-mail serait :

=INDEX(C1:C10, EQUIV(E1&";"&F1, D1:D10, 0))

Où :

• C1:C10 est la plage des adresses e-mail.
• E1 contient le nom à rechercher.
• F1 contient le prénom à rechercher.
• D1:D10 est la plage de la colonne combinée (nom et prénom).

Gestion des Erreurs

Si EQUIV ne trouve pas la valeur_recherchée, elle renvoie une erreur #N/A. Vous pouvez utiliser la fonction SIERREUR pour gérer cette erreur et afficher un message plus convivial.

Exemple :

=SIERREUR(INDEX(B1:B10, EQUIV("Produit X", A1:A10, 0)), "Produit non trouvé")

Cette formule renverra "Produit non trouvé" si "Produit X" n'est pas trouvé dans la colonne A.

Recherche Approximative

Bien que l'exemple principal utilise type = 0 (correspondance exacte) dans EQUIV, vous pouvez également utiliser type = 1 ou type = -1 pour effectuer des recherches approximatives. Cependant, assurez-vous que vos données sont correctement triées avant d'utiliser ces options.

Pièges Courants et Comment les Éviter

• Erreurs #REF! : Ces erreurs se produisent souvent si vous supprimez des lignes ou des colonnes référencées dans votre formule INDEX & EQUIV. Vérifiez attentivement vos références de plage.
• Erreurs #N/A : Ces erreurs indiquent que la valeur_recherchée n'a pas été trouvée dans la plage_recherche. Assurez-vous que la valeur existe et que la plage de recherche est correcte. Utilisez SIERREUR pour gérer ces erreurs.
• Mauvaise correspondance du type de données : Assurez-vous que le type de données de la valeur_recherchée correspond au type de données dans la plage_recherche. Par exemple, si vous recherchez un nombre, assurez-vous que les valeurs dans la plage de recherche sont également des nombres.

Conclusion

La combinaison des fonctions INDEX et EQUIV est un outil puissant et flexible pour effectuer des recherches avancées dans Excel. Bien qu'elle puisse sembler plus complexe que RECHERCHEV au premier abord, elle offre de nombreux avantages en termes de flexibilité, de robustesse et de capacité à gérer des structures de données complexes. En maîtrisant ces fonctions, vous améliorerez considérablement vos compétences en gestion de données dans Excel et deviendrez un utilisateur plus efficace.

N'hésitez pas à expérimenter avec différents exemples et à adapter les formules à vos besoins spécifiques. Avec un peu de pratique, vous maîtriserez rapidement INDEX & EQUIV et découvrirez tout leur potentiel.