INDEX et EQUIV dans Excel : l’alternative flexible à RECHERCHEV

RECHERCHEV est la première fonction de recherche que presque tout le monde apprend, et aussi celle qui pose le plus de problèmes : elle ne cherche que vers la droite, elle se casse quand quelqu’un insère une colonne et, si vous oubliez un argument, elle renvoie des résultats approximatifs qui semblent justes mais ne le sont pas.

La combinaison d’INDEX et d’EQUIV règle ces problèmes et fonctionne dans toutes les versions d’Excel. Vous verrez ce que fait chaque fonction séparément, comment les associer, comment chercher à la fois par ligne et par colonne et quelles erreurs surveiller.

Pourquoi chercher une alternative à RECHERCHEV

Une formule RECHERCHEV classique ressemble à ceci :

=RECHERCHEV(H2;A2:E6;4;FAUX)

Elle fonctionne, mais présente des limites importantes :

• Elle ne regarde que vers la droite. La valeur cherchée doit se trouver dans la première colonne de la plage. Impossible de chercher un nom et de renvoyer le code situé à sa gauche.
• Elle utilise un numéro de colonne fixe. Le 4 signifie « quatrième colonne ». Si quelqu’un insère une colonne au milieu, la formule renvoie une autre donnée sans prévenir.
• La correspondance approximative est l’option par défaut. Si vous omettez FAUX, Excel peut renvoyer une valeur proche au lieu d’une erreur.

Les données d’exemple

Nous utiliserons ce catalogue de produits dans les colonnes A à E, avec les en-têtes en ligne 1 :

Dans la cellule H2, nous saisirons le code à rechercher, par exemple P-104.

Les deux briques : INDEX et EQUIV

INDEX : renvoie la valeur d’une position

INDEX reçoit une plage et un numéro de ligne (et, si vous le souhaitez, de colonne) et renvoie ce qui se trouve à cette position :

=INDEX(B2:B6;3)

La formule renvoie « Écran », le troisième élément de la plage. Avec une plage de plusieurs colonnes, vous pouvez aussi indiquer la colonne :

=INDEX(A2:E6;3;4)

Elle renvoie 180 : ligne 3 et colonne 4 de la plage, c’est-à-dire le prix de l’écran.

EQUIV : trouve la position d’une valeur

EQUIV fait l’inverse : elle cherche une valeur dans une ligne ou une colonne et vous indique sa position.

=EQUIV("P-103";A2:A6;0)

Elle renvoie 3. Le dernier argument est le type de correspondance, et il est essentiel :

• 0 : correspondance exacte. C’est celui que vous utiliserez presque toujours.
• 1 (valeur par défaut) : la plus grande valeur inférieure ou égale à la valeur cherchée. Les données doivent être triées par ordre croissant.
• -1 : la plus petite valeur supérieure ou égale à la valeur cherchée. Les données doivent être triées par ordre décroissant.

Combiner INDEX et EQUIV

Le principe est simple : EQUIV calcule la ligne et INDEX renvoie la donnée de cette ligne. Pour obtenir le prix du code saisi en H2 :

=INDEX(D2:D6;EQUIV(H2;A2:A6;0))

Avec P-104 en H2, le résultat est 650. Pour construire la formule sans erreur :

1. Choisissez la colonne qui contient la réponse (D2:D6, les prix).
2. Choisissez la colonne qui contient la valeur que vous connaissez (A2:A6, les codes).
3. Vérifiez que les deux plages commencent et finissent sur la même ligne.
4. Écrivez EQUIV avec le 0 final et placez-la comme deuxième argument d’INDEX.

Comme la colonne de résultat est une plage indépendante, insérer des colonnes dans le tableau n’affecte pas la formule : Excel ajuste les références tout seul.

Rechercher vers la gauche

C’est le grand avantage par rapport à RECHERCHEV. Si vous connaissez le nom du produit (en H3) et voulez son code, situé à gauche :

=INDEX(A2:A6;EQUIV(H3;B2:B6;0))

Avec « Écran » en H3, la formule renvoie P-103.

Récupérer des données d’une autre feuille

Les plages peuvent se trouver sur des feuilles différentes. Si vous avez une liste de commandes avec le code en colonne A et le catalogue sur une feuille nommée Prix, cette formule récupère le prix de chaque commande :

=INDEX(Prix!D:D;EQUIV(A2;Prix!A:A;0))

Si vos données arrivent réparties dans plusieurs fichiers, par exemple un catalogue par fournisseur, mieux vaut d’abord les regrouper. Notre outil gratuit pour fusionner des fichiers Excel réunit plusieurs classeurs en un seul ; vous pouvez ensuite chercher avec une seule formule.

Recherche avec ligne et colonne variables

Avec deux EQUIV, l’un pour la ligne et l’autre pour la colonne, vous pouvez aussi choisir la donnée à afficher. Saisissez en H4 le nom d’une colonne, par exemple Stock :

=INDEX(A2:E6;EQUIV(H2;A2:A6;0);EQUIV(H4;A1:E1;0))

Avec P-104 en H2 et Stock en H4, le résultat est 8. Si vous remplacez H4 par Prix, vous obtenez 650 sans toucher à la formule. C’est un moyen très pratique de créer une petite fiche de consultation : une cellule pour le code, une liste déroulante avec les en-têtes et une formule.

Erreurs fréquentes et comment les éviter

• #N/A parce que la valeur n’existe pas. C’est souvent dû à des espaces en fin de texte, à des codes saisis différemment ou à des nombres stockés sous forme de texte (101 n’est pas la même chose que « 101 »). Pour afficher un message au lieu de l’erreur, encadrez la formule avec SIERREUR.
• Plages décalées. Si INDEX utilise D1:D6 et EQUIV utilise A2:A6, la position calculée correspond à une autre ligne et le résultat est décalé d’une position, sans aucun avertissement.
• Oublier le 0 dans EQUIV. Sans lui, Excel utilise la correspondance approximative et, avec des données non triées, peut renvoyer n’importe quelle ligne.
• Références qui bougent lors de la recopie. Si vous comptez recopier la formule vers le bas, figez les plages avec $, par exemple $A$2:$A$6.

Voici la version avec un message personnalisé :

=SIERREUR(INDEX(D2:D6;EQUIV(H2;A2:A6;0));"Introuvable")

Et RECHERCHEX ?

Excel 2021 et Microsoft 365 proposent RECHERCHEX, qui fait la même chose en une seule fonction et cherche dans n’importe quelle direction :

=RECHERCHEX(H2;A2:A6;D2:D6;"Introuvable")

Si vous partagez des fichiers avec des personnes qui utilisent Excel 2016 ou 2019, INDEX et EQUIV restent le choix le plus sûr : dans ces versions, RECHERCHEX affiche l’erreur #NOM?.

Conclusion

INDEX et EQUIV séparent deux tâches : trouver la position et renvoyer la donnée. C’est cette séparation qui les rend si flexibles. Elles cherchent dans toutes les directions, supportent les changements de structure du tableau et permettent des recherches par ligne et par colonne avec une seule formule.

Prenez l’habitude de toujours saisir le 0 d’EQUIV, de vérifier que les plages sont alignées et de nettoyer espaces et formats avant toute recherche. Ces trois réflexes vous éviteront la plupart des erreurs #N/A.

Questions fréquentes