INDEX EQUIV dans Excel : formule, exemples et erreurs courantes
Par Mirko Peters · · Mis à jour
INDEX EQUIV dans Excel expliqué : la formule, le fonctionnement de chaque fonction, les recherches à gauche et à double entrée, les critères multiples et les erreurs courantes, avec une comparaison avec RECHERCHEV.
Traduit automatiquement par IA à partir de l'article original. Lire l'original en anglais
INDEX EQUIV dans Excel est une formule de recherche construite à partir de deux fonctions : =INDEX(plage_renvoi; EQUIV(valeur_recherchée; plage_recherche; 0)). EQUIV détermine la position de votre valeur dans une liste, et INDEX renvoie l’élément situé à la même position dans une autre liste. Cet article s’adresse aux utilisateurs d’Excel qui écrivent des formules RECHERCHEV et se heurtent à ses limites, ou qui ont hérité d’un classeur rempli de formules INDEX EQUIV et souhaitent les comprendre. Vous découvrirez chaque fonction séparément, la manière dont elles s’articulent, comment rechercher vers la gauche, comment effectuer une recherche à double entrée, comment utiliser deux critères et ce que signifient les erreurs.
Points clés
- INDEX EQUIV associe deux fonctions dans une seule formule : =INDEX(plage_renvoi; EQUIV(valeur_recherchée; plage_recherche; 0)). EQUIV détermine la position, et INDEX renvoie l’élément situé à cette position.
- Utilisez 0 comme troisième argument d’EQUIV pour une correspondance exacte. Laissé à sa valeur par défaut de 1, EQUIV suppose que les données sont triées par ordre croissant.
- Comme les plages de recherche et de renvoi sont distinctes, INDEX EQUIV peut rechercher à gauche de la colonne de recherche, ce que RECHERCHEV ne peut pas faire.
- Pour deux conditions, vous pouvez soit regrouper les colonnes dans une colonne auxiliaire, soit utiliser une formule qui multiplie deux comparaisons à l’intérieur d’EQUIV.
- Encadrez la formule dans SI.NON.DISP pour remplacer #N/A par un message, tout en laissant apparaître les autres erreurs afin de ne pas masquer les véritables problèmes.
Vous trouverez INDEX EQUIV dans de nombreux classeurs anciens, car il fonctionnait dans toutes les versions d’Excel bien avant l’apparition de XLOOKUP. Il fonctionne toujours, et c’est la principale raison de l’apprendre. Les détails ci-dessous proviennent des pages Microsoft consacrées à EQUIV, à INDEX et à RECHERCHEV.
Ce que fait INDEX
INDEX renvoie une valeur à partir d’une position dans une plage. La page Microsoft présente la forme matricielle sous la forme INDEX(matrice; no_lig; [no_col]).
matriceest la plage dans laquelle la valeur est prélevée.no_ligest le numéro de ligne à l’intérieur de cette plage.no_colest le numéro de colonne à l’intérieur de cette plage. Si la plage ne comporte qu’une seule colonne, vous pouvez l’omettre.
Les numéros sont comptés à partir de la cellule supérieure gauche de la plage, et non de la feuille. L’exemple de Microsoft utilise un bloc dont la première colonne contient Pommes et Bananes, et la seconde Citrons et Poires. =INDEX(A2:B3;2;2) renvoie Poires, et =INDEX(A2:B3;2;1) renvoie Bananes.
Deux règles de la même page sont à retenir. Si no_lig ou no_col pointe en dehors de la plage, INDEX renvoie une erreur #REF!. Et si vous définissez no_lig ou no_col à 0, INDEX renvoie la colonne ou la ligne entière. Dans Microsoft 365 actuel, vous validez avec Entrée comme d’habitude. Dans les versions plus anciennes, vous saisissez la formule comme formule matricielle avec Ctrl+Maj+Entrée.
Seul, INDEX suppose que vous connaissiez déjà la position. C’est là qu’intervient EQUIV.
Ce que fait EQUIV
EQUIV recherche dans une plage et renvoie une position. Microsoft donne la syntaxe sous la forme EQUIV(valeur_cherchée; matrice_recherche; [type_coincidence]). Elle ne renvoie pas l’élément lui-même, uniquement sa position relative dans la plage.
Le troisième argument détermine le mode de correspondance :
0trouve la première correspondance exacte. La plage peut être dans n’importe quel ordre. C’est celle que vous utiliserez le plus souvent.1, qui est aussi la valeur par défaut si vous omettez l’argument, trouve la plus grande valeur inférieure ou égale à la valeur recherchée. La plage doit être triée par ordre croissant.-1trouve la plus petite valeur supérieure ou égale à la valeur recherchée. La plage doit être triée par ordre décroissant.
Les exemples de Microsoft utilisent les valeurs 25, 38, 40 et 41 en B2:B5. =MATCH(39,B2:B5,1) renvoie 2, la position de 38, qui est la valeur immédiatement inférieure. =MATCH(41,B2:B5,0) renvoie 4. Et =MATCH(40,B2:B5,-1) renvoie #N/A, car ces valeurs ne sont pas triées par ordre décroissant.
Quelques autres comportements relevés sur cette page : la recherche de texte ne tient pas compte de la casse, donc « apple » et « APPLE » sont identiques. Avec un type de correspondance 0 et une valeur recherchée textuelle, ? remplace n’importe quel caractère unique et * n’importe quelle suite de caractères. Un tilde placé devant l’un de ces caractères permet de rechercher le caractère littéral. Si MATCH ne trouve rien, il renvoie #N/A.
La valeur par défaut de 1 piège beaucoup de monde. Si vous oubliez le troisième argument, MATCH suppose silencieusement que les données sont triées et peut renvoyer une mauvaise position sans aucune erreur. Dans presque toutes les recherches d’identifiants, de noms ou de codes, saisissez le 0.
Assemblons le tout
Le schéma est toujours le même : MATCH s’insère dans INDEX et fournit le numéro de ligne.
=INDEX(return_range, MATCH(lookup_value, lookup_range, 0))
Prenons une feuille avec des identifiants d’employés en A2:A100, des noms en B2:B100 et des services en C2:C100. Vous saisissez un identifiant en F2 et souhaitez obtenir le service correspondant.
- Cliquez sur la cellule du résultat, par exemple G2.
- Saisissez
=INDEX(C2:C100,pour indiquer d’où provient la réponse. - Ajoutez
MATCH(F2,A2:A100,0))pour indiquer où trouver l’identifiant. - Appuyez sur Entrée.
La formule finale est =INDEX(C2:C100,MATCH(F2,A2:A100,0)). MATCH trouve F2 dans A2:A100 et renvoie, par exemple, 14. INDEX renvoie alors le 14e élément de C2:C100.
Gardez des plages de même longueur et de même ligne de départ. Si MATCH parcourt A2:A100 et que INDEX lit C3:C101, la position pointe vers la mauvaise ligne et le résultat est faux sans aucun message d’erreur. Utilisez les mêmes lignes de début et de fin pour les deux, et verrouillez-les avec le signe dollar, $A$2:$A$100, si vous prévoyez de copier la formule vers le bas.
Rechercher vers la gauche, et ce qui change par rapport à RECHERCHEV
La page RECHERCHEV de Microsoft donne la syntaxe VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]). Elle indique que la valeur recherchée doit se trouver dans la première colonne de la plage, et que l’omission de range_lookup donne une correspondance approximative, qui suppose des données triées. La page suggère aussi d’essayer XLOOKUP.
Voici la même opération réalisée de deux façons, avec des identifiants en colonne A et des noms en colonne B :
=VLOOKUP(F2,A2:B100,2,FALSE)=INDEX(B2:B100,MATCH(F2,A2:A100,0))
INDEX MATCH présente deux avantages réels. D’abord, il peut rechercher vers la gauche. Si les noms se trouvent en colonne B et que vous voulez récupérer l’identifiant de la colonne A, il suffit d’inverser les plages : =INDEX(A2:A100,MATCH(F2,B2:B100,0)). RECHERCHEV ne peut pas faire cela, car sa valeur recherchée doit se trouver dans la première colonne. Ensuite, INDEX MATCH désigne directement la colonne de résultat au lieu de la compter. Dans une RECHERCHEV, le 2 de la formule signifie « deuxième colonne de la plage ». Insérez une colonne dans la plage et ce nombre pointe vers une autre colonne. Avec INDEX MATCH, la référence s’ajuste comme n’importe quelle autre référence.
Vous lirez aussi qu’INDEX MATCH est plus rapide. Les pages de Microsoft ne font pas une telle affirmation, et l’écart éventuel ne compte que dans des classeurs très volumineux. Ne le choisissez donc pas pour la vitesse, mais pour sa souplesse.
Recherches à double entrée
Une recherche à double entrée renvoie la valeur située à l’intersection d’une ligne et d’une colonne, par exemple les ventes d’un produit pour un mois donné. Utilisez deux fonctions MATCH, l’une pour la ligne et l’autre pour la colonne :
=INDEX(B2:M20,MATCH(P1,A2:A20,0),MATCH(Q1,B1:M1,0))
Ici, B2:M20 contient les nombres, A2:A20 les noms de produits, B1:M1 les en-têtes de mois, et P1 et Q1 le produit et le mois recherchés. Le premier MATCH donne la ligne, le second la colonne, et INDEX renvoie le nombre situé à leur croisement. Notez que la plage de retour commence en B2, et non en A1 : ainsi les positions correspondent aux plages d'étiquettes, car les deux plages de MATCH commencent aussi à la même ligne ou colonne que les nombres.
Recherche sur deux critères
MATCH n'accepte qu'une seule valeur de recherche. Si un enregistrement n'est identifié que par deux champs, comme la catégorie et le statut, vous disposez de deux options.
Une colonne auxiliaire. Ajoutez une colonne qui concatène les deux champs, par exemple =A2&"|"&B2 en D2, puis recopiez la formule vers le bas. Recherchez ensuite le texte concaténé :
=INDEX(C2:C100,MATCH(F2&"|"&G2,D2:D100,0))
Le séparateur est important. Sans lui, « AB » suivi de « C » et « A » suivi de « BC » donnent tous deux « ABC ». La colonne auxiliaire est facile à lire et à vérifier, et elle fonctionne dans toutes les versions.
Une formule matricielle. Sans colonne auxiliaire, vous pouvez multiplier deux comparaisons :
=INDEX(C2:C100,MATCH(1,(A2:A100=F2)*(B2:B100=G2),0))
Chaque comparaison donne VRAI ou FAUX. La multiplication ne donne 1 que lorsque les deux sont vraies, et MATCH recherche le premier 1. Cette approche ne figure pas dans les pages de Microsoft consacrées à INDEX et MATCH : testez-la donc sur une petite plage. Dans les anciennes versions d'Excel, vous devrez peut-être la saisir avec Ctrl+Maj+Entrée, comme Microsoft le décrit pour les résultats matriciels de INDEX. Dans Microsoft 365 actuel, la touche Entrée suffit.
Caractères génériques et valeurs les plus proches
Avec un type de correspondance 0 et du texte, MATCH accepte les caractères génériques. =INDEX(B2:B100,MATCH("Jan*",A2:A100,0)) renvoie l'élément situé à côté de la première entrée qui commence par « Jan ». C'est utile pour les codes partiels. Rendez votre motif précis, car MATCH s'arrête à la première correspondance.
Pour des paliers, comme des tranches d'imposition ou des barèmes de commission, utilisez volontairement le type de correspondance 1 par défaut, avec des seuils triés par ordre croissant. Placez 0, 1000 et 5000 en A2:A4 et les taux en B2:B4 ; =INDEX(B2:B4,MATCH(E2,A2:A4,1)) renvoie le taux du plus grand seuil qui ne dépasse pas E2. C'est l'un des rares cas où la valeur par défaut avec tri est le bon choix.
Corriger les erreurs
- #N/A. MATCH n'a rien trouvé. Recherchez les fautes de frappe, les espaces en fin de texte et les nombres stockés sous forme de texte, car « 100 » et 100 sont différents. Vérifiez que le type de correspondance est 0, et que les types de correspondance sur données triées (1 ou -1) portent bien sur des données triées.
- #REF!. INDEX a demandé une ligne ou une colonne en dehors de la plage. Cela arrive aussi lorsque vous supprimez une ligne ou une colonne utilisée par la formule. Vérifiez que les plages de MATCH et d'INDEX ont la même taille.
- Un résultat faux sans aucune erreur. C'est généralement un 0 manquant dans MATCH, ou des plages qui ne commencent pas sur les mêmes lignes.
- Un résultat faux après recopie. Les plages se sont décalées parce qu'elles n'étaient pas verrouillées. Ajoutez
$aux plages.
Pour remplacer une erreur par un message, la fonction IFNA de Microsoft renvoie la valeur de votre choix lorsqu'une formule génère #N/A, et laisse passer inchangée toute autre erreur :
Privilégiez IFNA à IFERROR pour les recherches. IFERROR masque toutes les erreurs, y compris un #REF! causé par une plage cassée ; un vrai problème ressemble alors à un simple message « Not found ». IFNA ne couvre que le cas que vous attendez. Microsoft indique IFNA pour Excel 2016 et versions ultérieures.
Privilégiez IFNA à IFERROR pour les recherches. IFERROR masque toutes les erreurs, y compris un #REF! causé par une plage cassée, si bien qu’une véritable anomalie ressemble à un simple « Introuvable ». IFNA ne couvre que le cas que vous attendez. La page de Microsoft indique qu’Excel 2016 est la version la plus ancienne concernée.
INDEX MATCH ou XLOOKUP
Si tous ceux qui ouvrent votre fichier disposent de Microsoft 365 à jour, XLOOKUP accomplit la même tâche avec une seule fonction et un message intégré pour les valeurs introuvables. Nous l'abordons dans notre guide sur XLOOKUP dans Excel. Mais la page de Microsoft consacrée à XLOOKUP ne précise pas clairement la compatibilité avec Excel 2016 et 2019, et elle ne mentionne pas Excel pour le web. INDEX MATCH fonctionne dans tous ces environnements. Le choix est donc pragmatique : utilisez XLOOKUP dans les fichiers destinés aux versions actuelles, et INDEX MATCH lorsque le fichier circule auprès de personnes disposant de versions plus anciennes, ou lorsque vous maintenez un classeur qui l'utilise déjà.
Erreurs courantes
- Oublier le
0dans MATCH et se fier à la valeur par défaut. - Utiliser pour INDEX et MATCH des plages de tailles différentes ou commençant à des lignes différentes.
- Oublier de verrouiller les plages avec
$avant de recopier la formule. - Concaténer des critères sans séparateur dans une colonne auxiliaire.
- Encapsuler tout dans IFERROR, si bien que des formules cassées ressemblent à des données manquantes.
- Vous pensez que INDEX EQUIV renvoie toutes les correspondances. Il ne renvoie que la première.
Sujets connexes à explorer
- XLOOKUP. La fonction de recherche la plus récente, qui intègre le sens de recherche et un argument pour le cas où la valeur est introuvable.
- Plusieurs résultats à la fois. INDEX EQUIV ne renvoie que la première correspondance. La fonction FILTRE est l'outil à utiliser pour renvoyer toutes les correspondances dans les versions qui la proposent.
- Tableaux et plages nommées. Les références structurées et les noms rendent les recherches plus lisibles et permettent à la plage de s'étendre avec vos données.
- Performances sur les grandes feuilles. Utilisez des plages délimitées plutôt que des colonnes entières, et gardez les plages aussi restreintes que possible.
- Recherches sensibles à la casse. INDEX EQUIV ne tient pas compte de la casse : une recherche sensible à la casse nécessite une autre approche. Testez toute solution de contournement avant de vous y fier.
Prochaine étape
Réécrivez l'une de vos formules RECHERCHEV en INDEX EQUIV et comparez le résultat sur quelques valeurs connues. Essayez ensuite la même formule avec une recherche vers la gauche. Une fois ces deux tests effectués, vous saurez quand INDEX EQUIV dans Excel est le meilleur outil pour la tâche.
Sources
- Fonction MATCH (Microsoft Support)
- Fonction INDEX (Microsoft Support)
- Fonction VLOOKUP (Microsoft Support)
- Fonction IFNA (Microsoft Support)
- Fonction XLOOKUP (Microsoft Support)
FAQ
Comment fonctionne INDEX EQUIV dans Excel ?
EQUIV recherche votre valeur dans une plage et renvoie sa position, par exemple 5 pour le cinquième élément. INDEX utilise ensuite ce nombre pour renvoyer l’élément situé à la même position dans une autre plage. Ensemble, =INDEX(C2:C100;EQUIV(F2;A2:A100;0)) recherche F2 dans la colonne A et renvoie la valeur de la colonne C.
INDEX EQUIV est-il meilleur que RECHERCHEV ?
Il est plus souple. RECHERCHEV exige que la valeur recherchée se trouve dans la première colonne de sa plage et compte les colonnes par leur numéro, alors qu’INDEX EQUIV utilise deux plages distinctes : il peut donc rechercher vers la gauche et n’est pas affecté par l’insertion de colonnes. Pour une recherche simple vers la droite, les deux conviennent. XLOOKUP constitue une troisième option dans les versions récentes.
Pourquoi INDEX EQUIV renvoie-t-il #N/A ?
EQUIV renvoie #N/A lorsqu’il ne trouve aucune correspondance. Vérifiez les fautes de frappe, les espaces superflus, les nombres stockés sous forme de texte et le type de correspondance. Utilisez 0 pour une correspondance exacte. Avec la valeur par défaut de 1, la plage de recherche doit être triée par ordre croissant, et avec -1, elle doit être triée par ordre décroissant.