INDEX et EQUIV : la recherche flexible, jusqu’au double critère

RECHERCHEX est élégante, mais elle n’existe pas partout. Quand un classeur doit circuler entre des collègues qui n’ont pas tous la dernière version d’Excel, le couple INDEX et EQUIV reste la solution universelle. Il s’emploie aussi pour une famille de problèmes que RECHERCHEV ne traite pas : lire un tableau à double entrée.

Ce que vous saurez faire à la fin de l’épisode

• expliquer le rôle d’INDEX et celui d’EQUIV ;
• les combiner pour retrouver une valeur ;
• croiser une ligne et une colonne d’une matrice ;
• retrouver une valeur selon deux critères ;
• éviter les décalages de plage qui faussent le résultat.

Les deux fonctions

EQUIV (anglais : MATCH) donne la position d’une valeur dans une plage d’une seule colonne ou d’une seule ligne :

=EQUIV(valeur_cherchée; plage; 0)

Dans la liste Janvier, Février, Mars, Avril, Mai, Juin, EQUIV("Mars";…;0) renvoie 3.

INDEX renvoie la valeur située à une position donnée d’une plage :

=INDEX(plage; n°_ligne; [n°_colonne])

INDEX(A2:A7; 3) renvoie le troisième élément de la plage, soit « Mars ». L’association des deux se comprend ainsi : EQUIV trouve où, INDEX lit quoi. L’équivalent de RECHERCHEV(clé; table; 2; FAUX) s’écrit :

=INDEX(colonne_résultat; EQUIV(clé; colonne_clé; 0))

La colonne de résultat et la colonne clé sont deux plages distinctes : la clé peut donc se trouver n’importe où, à gauche comme à droite. Pas de numéro de colonne à compter, pas de plage qui se décale si l’on insère une colonne.

Croiser une ligne et une colonne

Atlas Négoce suit son chiffre d’affaires par mois (en lignes) et par produit (en colonnes). Le gérant pose des questions, qui se ramènent à retrouver le contenu d’une cellule de la matrice.

Figure 1 : la matrice A1:E7 et quatre questions en colonnes G et H.

« Quel est le CA de mars en informatique ? »

=INDEX($B$2:$E$7; EQUIV("Mars";$A$2:$A$7;0); EQUIV("Informatique";$B$1:$E$1;0))

Le premier EQUIV trouve la ligne de mars (3), le second la colonne d’Informatique (3) ; INDEX lit la cellule à l’intersection : 150 700. En pratique, « Mars » et « Informatique » ne sont pas écrits en dur mais pris dans des cellules, ce qui transforme la formule en petit tableau de consultation.

« Et juin en mobilier ? » Même formule : ligne 6, colonne 1, résultat 158 700.

« Quel est le meilleur mois en Services ? »

=INDEX($A$2:$A$7; EQUIV(MAX($E$2:$E$7); $E$2:$E$7; 0))

MAX trouve la valeur la plus élevée de la colonne Services (64 000), EQUIV sa position (5), INDEX le mois correspondant : Mai. De même, le mois du plus bas chiffre en Mobilier (MIN) est Juin (158 700). C’est la méthode pour retrouver « qui » ou « quand » à partir d’un maximum ou d’un minimum, que MAX seule ne donne pas.

Chercher sur deux critères

Autre cas courant : un tableau de factures où le même client apparaît pour plusieurs produits, et l’on veut le montant du couple (client, produit). RECHERCHEV ne sait chercher qu’une clé.

Figure 2 : la colonne D assemble les deux critères (repère 1) ; la recherche porte sur cette clé (repère 2).

Étape 1 : une colonne clé. En D2 : =A2&"|"&B2, qui donne par exemple Maroc Équipements|Informatique. Le séparateur | évite des collisions entre deux couples (« AB » + « C » contre « A » + « BC »).

Étape 2 : la recherche.

=INDEX($C$2:$C$6; EQUIV(F2&"|"&G2; $D$2:$D$6; 0))

On reconstruit la clé avec les deux critères, et l’on cherche sa position : le couple Maroc Équipements | Informatique est en 3e ligne, le montant est 14 400. Les couples (Maroc Équipements, Mobilier) apparaissent deux fois (6 000 et 7 800) : la recherche renvoie la première, ce qui n’est pas une erreur du tableau mais de la question : si un couple peut figurer plusieurs fois, ce n’est plus une recherche, c’est un cumul (SOMME.SI.ENS, épisode 23).

Sans colonne auxiliaire, une formule matricielle fait la même chose :

=INDEX($C$2:$C$6; EQUIV(1; ($A$2:$A$6=F2)*($B$2:$B$6=G2); 0))

Chaque comparaison produit une série de VRAI/FAUX ; leur produit vaut 1 là où les deux critères sont vrais ; EQUIV(1;…;0) trouve la première ligne correspondante. Dans Microsoft 365 et Excel 2021, elle se valide normalement ; dans une version plus ancienne, il faut la valider avec Ctrl + Maj + Entrée. Pour un classeur partagé, la colonne clé est plus transparente.

Les pièges d’INDEX/EQUIV

• Oublier le 0 d’EQUIV. Le troisième argument par défaut (1) lance une recherche approchée sur plage triée : sur une plage non triée, la position renvoyée est fausse sans message. Écrivez toujours 0.
• Plages de tailles ou de départs différents. INDEX($B$2:$B$7; EQUIV(x; $A$3:$A$8; 0)) est décalée d’une ligne : EQUIV renvoie une position relative à A3:A8, INDEX la lit dans B2:B7. Les deux plages doivent commencer à la même ligne.
• Types incompatibles (texte contre nombre) : même diagnostic que pour RECHERCHEV (épisode 19).
• Doublons dans la clé : seule la première occurrence est trouvée.
• Plages non figées : $ obligatoires pour recopier la formule.

Quelle fonction choisir ?

À vous de jouer

Dans le classeur d’exercice :

1. Onglet Matrice : complétez H2:H5 avec les quatre formules. Vérifiez chaque résultat à l’œil.
2. Modifiez les critères « Mars » et « Informatique » par des cellules de saisie avec liste déroulante (épisode 11).
3. Ajoutez un mois (juillet) en ligne 8 : vos formules le voient-elles ? Corrigez-les en étendant les plages.
4. Onglet Double critère : retrouvez le montant de (Atlas Export, Mobilier). Puis de (Maroc Équipements, Mobilier) : que renvoie la formule ? Que concluez-vous ?

Correction. Les réponses sont 150 700, 158 700, Mai et Juin. Les plages $A$2:$A$7 s’arrêtent en ligne 7 : le mois de juillet, en ligne 8, n’est pas vu tant qu’on n’étend pas les plages à $A$2:$A$8 et $B$2:$E$8 (les tableaux Excel règlent ce problème). Pour (Atlas Export, Mobilier), le montant est 3 300. Pour (Maroc Équipements, Mobilier), la formule renvoie 6 000 (première occurrence) alors qu’il existe aussi 7 800 : en présence de doublons, il faut cumuler (13 800) et non rechercher.

À retenir

• EQUIV trouve la position, INDEX lit la valeur à cette position.
• Le troisième argument d’EQUIV : toujours 0.
• INDEX(table; EQUIV(ligne); EQUIV(colonne)) croise lignes et colonnes ; INDEX(…; EQUIV(MAX(…))) retrouve le « qui » d’un maximum.
• Deux critères : une colonne clé assemblée avec &, ou une formule matricielle.
• Une clé qui se répète n’appelle pas une recherche mais un cumul.

Épisode 22 : passons des recherches aux cumuls conditionnels avec SOMME.SI, NB.SI et MOYENNE.SI.