Les fonctions INDEX et EQUIV forment un duo incontournable pour dépasser les limites de RECHERCHEV. Ensemble, ils permettent des recherches plus flexibles, plus robustes et adaptées aux bases de données réelles, même lorsque vos tableaux évoluent.
Cet article vous guide à travers l'utilisation combinée de ces fonctions pour une extraction de données efficace et rapide. L'usage d'Index Equiv plutôt que Recherche V peut véritablement simplifier et améliorer les recherches dans Excel. Index Equiv offre une combinaison puissante pour effectuer des recherches plus avancées, flexibles et rapides.
Utilisez INDEX + EQUIV conjointement
Comprendre les Fonctions INDEX et EQUIV
Avant de plonger dans les exemples pratiques, il est essentiel de comprendre le rôle de chaque fonction :
- INDEX : Cette fonction sert à extraire une donnée précise d’un tableau ou d’une plage de cellules. Elle va chercher la valeur située à l’intersection d’une ligne et, si vous le précisez, d’une colonne.
- EQUIV : Cette fonction ne renvoie pas une valeur, mais la position d’un élément dans une plage. En d’autres termes, elle vous dit où se trouve la donnée que vous recherchez. Par exemple, si vous cherchez le produit « Clavier » dans une liste de 50 articles, EQUIV peut vous dire qu’il se trouve en 17ᵉ position dans la colonne.
L’objectif d’INDEX est simple : récupérer la valeur située à une position précise d’une plage (ligne, et éventuellement colonne). L’objectif d’EQUIV est de trouver la position d’une valeur dans une plage donnée.
Seul, INDEX a besoin d’un numéro de ligne ou de colonne pour extraire une valeur. Seule, la fonction INDEX permet d’aller chercher une donnée précise à un emplacement fixe.
💡 Astuce pro : INDEX devient redoutable une fois combiné avec EQUIV (EQUIV trouve la position, INDEX renvoie la valeur). 💡 Astuce pro : EQUIV est souvent utilisée seule pour trouver rapidement la position, mais elle devient vraiment puissante quand vous l’associez à INDEX : EQUIV localise, INDEX affiche.
Pourquoi Utiliser INDEX et EQUIV Plutôt Que RECHERCHEV ?
La fonction RECHERCHEV est souvent utilisée pour rechercher une valeur dans la première colonne d'un tableau, puis renvoyer une valeur dans la même ligne d'une colonne spécifiée. Elle présente plusieurs limitations, comme la nécessité que la valeur recherchée se trouve dans la première colonne. De plus, elle renvoie une erreur #N/A si aucune correspondance n'est trouvée.
INDEX et EQUIV offrent une plus grande flexibilité en comparaison à RECHERCHEV. L'utilisation combinée d'INDEX et EQUIV permet de contourner les limitations de RECHERCHEV, comme la nécessité de l'ordre des colonnes. Par exemple, =INDEX(Ligne ou tableau, EQUIV(valeur cherchée, Colonne de recherche, 0)) permet de rechercher des valeurs n'importe où dans le tableau, augmentant ainsi la flexibilité et réduisant les erreurs #N/A.
Voici les principaux avantages d'INDEX et EQUIV :
- Flexibilité: Permet des recherches flexibles (gauche/droite, haut/bas).
- Robustesse: Peu sensible aux colonnes déplacées.
- Précision: Possibilité de recherches multi-critères.
Comment Combiner INDEX et EQUIV : Exemples Pratiques
Pour illustrer l'utilisation combinée d'INDEX et EQUIV, prenons un exemple concret :
Imaginons que vous souhaitiez savoir quelles seront les ventes sur le marché 2 en année fiscale 2017. Il suffit d’indiquer à INDEX que nous considérons la totalité du tableau (D4:D8), et que le résultat qui nous intéresse se trouve en ligne 4 et en colonne 3.
Par exemple, si nous cherchons à connaitre l’emplacement de la valeur “FY2017” dans notre tableau, il suffira de dire à MATCH : “cherche la valeur ‘FY2017’, dans cette colonne, et dès que tu trouves cette valeur exacte donne-moi le résultat”.
Vous l’aurez compris, ce qui marche en colonne, marche également en ligne. Si nous cherchons l’emplacement de la valeur “Market 4″, il suffira de structurer notre fonction match de la même manière mais en sélectionnant la première ligne : ” =MATCH(“Market 4″;D4:H4;0)”.
Repartons donc de INDEX, il suffit de sélectionner le même tableau (D4:H8 dans notre exemple). La formule est un peu longue, mais si vous avez bien suivi les étapes précédentes, il n’y a rien de difficile.
Exemple détaillé
Afin d'illustrer une utilisation concrète, prenons l'exemple d'un tableau contenant des tarifs par catégorie de joueurs et par type de licence. L'objectif est d'afficher le montant de la cotisation pour un joueur donné en fonction de sa catégorie et de sa licence.
-
Fonction INDEX :
- Indiquer la table matrice qui contient les données (par exemple, de B2 à D5).
- Indiquer le numéro de la ligne où se situe la catégorie recherchée.
- Indiquer dans quelle colonne de la matrice de recherche se situe la licence.
-
Fonction EQUIV :
- Chercher dans une plage et renvoyer la position de l’élément trouvé dans cette même plage.
- Par exemple, chercher dans la plage B2:D2 le mot LOISIR et obtenir la réponse 2.
La combinaison de ces deux fonctions permet d'automatiser la recherche et d'afficher le montant correct de la cotisation en fonction des critères spécifiés.
Recherches Multi-Critères avec INDEX et EQUIV
Normalement, la solution INDEX + EQUIV ne permet pas l’utilisation de plusieurs critères. Il est possible de contourner le problème en créer une colonne qui concatène les différentes « colonnes-critères ». La solution vient de l’utilisation d’une formule matricielle.
Pour rechercher des valeurs avec INDEX + EQUIV tout en appliquant plusieurs critères de recherche, il faudra utiliser une formule matricielle. On va créer un tableau composé de 0 et 1 basé sur les critères souhaités. Comme dans notre exemple, plusieurs enregistrements peuvent correspondre aux différents critères saisis.
Une méthode simple consiste à créer une colonne auxiliaire où vous concaténez les critères (ex. Produit & "-" & Mois). Vous recherchez ensuite cette clé unique avec INDEX + EQUIV. Cela évite les formules lourdes et améliore la lisibilité.
Améliorer la Lisibilité et la Performance des Formules
Pour améliorer la lisibilité des formules, commencez par nommer vos plages afin de remplacer les références brutes par des appellations claires comme ListeProduits ou PrixUnitaires. Vous pouvez également ajouter des commentaires dans les cellules pour expliquer en quelques mots le rôle de la formule, ce qui facilite la compréhension pour les autres utilisateurs.
Enfin, privilégie les tableaux structurés : ils rendent les formules beaucoup plus intuitives, presque auto-explicatives, comme dans l’exemple =INDEX(Articles[Prix]; EQUIV(E2; Articles[Nom]; 0)).
Pour optimiser les performances, il est conseillé de limiter les plages de recherche à la taille utile (éviter A:A) et de transformer vos données en tableaux structurés (Ctrl+T). Sur des fichiers dépassant plusieurs dizaines de milliers de lignes, INDEX + EQUIV reste plus rapide et plus fiable que RECHERCHEV.
Erreurs Courantes et Comment les Éviter
Même avec INDEX et EQUIV, certaines erreurs peuvent bloquer ou fausser vos résultats. Voici quelques erreurs courantes et leurs solutions :
| Erreur | Cause | Solution |
|---|---|---|
| #N/A | La valeur recherchée n'est pas trouvée. Les espaces invisibles et casses différentes créent des #N/A. | Utilisez les fonctions CLEAN ou TRIM pour supprimer les caractères inattendus ou les espaces masqués. |
| #REF! | L’indice dépasse la taille de la plage. | Vérifiez que les références de ligne et de colonne dans INDEX et EQUIV sont correctes et ne dépassent pas les limites de la plage de données. |
| Formule matricielle incorrecte | Lorsque vous utilisez un tableau dans INDEX, MATCH ou une combinaison de ces deux fonctions, il est nécessaire d’appuyer sur Ctrl+Maj+Entrée sur le clavier. | Si vous disposez d’une version actuelle de Microsoft 365, vous pouvez simplement entrer la formule dans la cellule de sortie, puis appuyer sur Entrée pour confirmer la formule en tant que formule de tableau dynamique. Sinon, la formule doit être entrée en tant que formule matricielle héritée en sélectionnant d’abord la plage de sortie, en entrant la formule dans la cellule de sortie, puis en appuyant sur Ctrl+Maj+Entrée pour la confirmer. |
| Incohérence dans l'ordre de tri | Lorsque vous utilisez MATCH, il doit y avoir une cohérence entre la valeur dans l’argument match_type et l’ordre de tri des valeurs dans le tableau de recherche. | Remplacez l’argument match_type par 1 ou triez le tableau au format décroissant. |
| Plusieurs conditions non gérées | INDEX et EQUIV ne gèrent pas nativement plusieurs conditions. | Créez une colonne auxiliaire où vous concaténez les critères. Vous recherchez ensuite cette clé unique avec INDEX + EQUIV. |
💡 Astuce bonus : 80 % des erreurs viennent de données mal préparées.
Alternatives et Compléments à INDEX et EQUIV
RECHERCHEX (XLOOKUP en anglais) est la version moderne et simplifiée d’INDEX + EQUIV. Elle intègre nativement la recherche à gauche/droite, la gestion d’erreurs et la flexibilité des plages. Mais INDEX + EQUIV reste utile pour comprendre la logique des recherches et fonctionne sur toutes les versions d’Excel, ce qui n’est pas encore le cas de RECHERCHEX.
Absolument. Vous pouvez par exemple l’associer à SIERREUR pour sécuriser les résultats, l’utiliser avec SOMME.SI.ENS ou NB.SI.ENS pour croiser recherche et agrégation, ou encore le combiner avec FILTRE (Excel 365) afin d’afficher plusieurs résultats en une seule formule.
Si vous voulez aller plus loin, la formation Excel d’Educademy vous accompagne pas à pas : de la découverte des fonctions de base à l’apprentissage des outils avancés comme RECHERCHEX, FILTRE ou XLOOKUP. Vous y trouverez des exemples pratiques, des exercices guidés et des astuces de pro pour gagner en efficacité et éviter les erreurs courantes.
L’article « Comment utiliser la fonction INDEX et EQUIV dans Excel ? » a été noté 4,7 sur 5 par 229 avis d’internautes.