Recherches bidirectionnelles avec INDEX-MATCH-MATCH

Imaginez une grille de ventes mensuelles où les régions sont disposées à gauche, de haut en bas, et les mois sur la ligne supérieure, de gauche à droite. Vous voulez obtenir le nombre situé à l’intersection d’une région et d’un mois choisis.

Une recherche classique trouve une valeur en parcourant une seule direction. Une recherche bidirectionnelle parcourt les deux directions à la fois : elle trouve la bonne ligne et la bonne colonne, puis renvoie la valeur située à leur intersection.

L’outil classique pour cela est INDEX combiné à deux appels à MATCH, souvent sous la forme INDEX-MATCH-MATCH.

INDEX renvoie une valeur d’une plage en fonction de sa position. Sa forme complète est INDEX(array, row_num, column_num).

Fournissez-lui un bloc de cellules, un numéro de ligne et un numéro de colonne : elle renvoie la valeur située à cet emplacement. Par exemple, dans une grille commençant en B2, demander la ligne 3 et la colonne 2 renvoie la valeur située 3 lignes plus bas et 2 colonnes plus à droite à l’intérieur de ce bloc.

L’idée essentielle est la suivante : INDEX a besoin de positions, pas d’étiquettes. C’est précisément ce que fournit MATCH.

MATCH trouve la position d’une valeur dans une seule ligne ou colonne. Sa forme est MATCH(lookup_value, lookup_array, match_type).

Utilisez 0 comme type de correspondance pour une correspondance exacte. Le résultat est un nombre indiquant la position de la valeur, en comptant à partir de 1.

Si « East » est le deuxième élément de la plage A2:A5, MATCH renvoie 2. Ce 2 peut devenir le numéro de ligne utilisé par INDEX.

Pour effectuer une recherche bidirectionnelle, vous exécutez MATCH deux fois :

• Un MATCH trouve la ligne dans laquelle se trouve votre région.
• Un MATCH trouve la colonne dans laquelle se trouve votre mois.

Vous transmettez ensuite ces deux nombres à INDEX. Le MATCH de ligne recherche dans une plage verticale d’étiquettes ; le MATCH de colonne recherche dans une plage horizontale d’en-têtes.

Le résultat est la cellule unique située à l’intersection de cette ligne et de cette colonne.

Imaginez la disposition suivante. Les étiquettes des régions se trouvent dans A2:A5 (East, West, North, South). Les en-têtes des mois se trouvent dans B1:D1 (Jan, Feb, Mar). Les chiffres de ventes proprement dits remplissent B2:D5.

Deux cellules d’entrée pilotent la recherche : G1 contient la région souhaitée et G2 le mois souhaité.

Notre objectif est de créer une seule formule qui lit G1 et G2 et renvoie le chiffre de ventes correspondant dans B2:D5.

Commencez par localiser la région. MATCH recherche dans la liste verticale d’étiquettes A2:A5 la valeur saisie dans G1.

Si G1 contient « North » et que North est la troisième étiquette, ce MATCH renvoie 3.

Ce nombre indique à INDEX quelle ligne du bloc de données lire. Remarquez que nous recherchons A2:A5, uniquement les étiquettes, et non les données : la position 3 correspond ainsi à la troisième ligne de données.

Localisez ensuite le mois. Ce MATCH recherche dans la ligne d’en-têtes horizontale B1:D1 la valeur contenue dans G2.

Si G2 contient « Feb » et que Feb est le deuxième en-tête, MATCH renvoie 2.

Ce nombre devient la position de colonne utilisée par INDEX. Comme pour les lignes, nous recherchons uniquement les en-têtes B1:D1, afin que la position corresponde aux colonnes de données de B2:D5.

Intégrez maintenant les deux appels à MATCH dans INDEX. Le bloc de données B2:D5 constitue le tableau, le MATCH de ligne fournit le numéro de ligne et le MATCH de colonne fournit le numéro de colonne.

Lorsque G1 contient « North » et G2 « Feb », le MATCH de ligne renvoie 3 et celui de colonne renvoie 2. INDEX renvoie donc la valeur située à la ligne 3 et à la colonne 2 de B2:D5.

Cette formule unique constitue la recherche bidirectionnelle complète.

Supposons que B2:D5 contienne ceci : la ligne de North indique Jan 50, Feb 80 et Mar 65.

• MATCH("North", A2:A5, 0) renvoie 3.
• MATCH("Feb", B1:D1, 0) renvoie 2.
• INDEX(B2:D5, 3, 2) lit la ligne 3 et la colonne 2, et renvoie 80.

Remplacez G1 par « East » ou G2 par « Mar » : toute la formule se recalcule instantanément. C’est tout l’intérêt de piloter INDEX avec deux recherches MATCH.

VLOOKUP recherche uniquement dans la première colonne et renvoie une valeur située à un nombre fixe de colonnes vers la droite. Pour changer de mois, vous devriez coder en dur ou calculer vous-même l’index de colonne.

INDEX-MATCH-MATCH permet de choisir dynamiquement la ligne et la colonne à partir de leur libellé. Réorganisez les colonnes ou insérez de nouveaux mois : la formule continue de fonctionner, car elle établit la correspondance avec le texte de l’en-tête, et non avec un nombre fixe.

L’erreur la plus courante concerne les plages qui ne correspondent pas. La plage de lignes de MATCH doit avoir la même hauteur que le bloc de données d’INDEX, et la plage de colonnes de MATCH doit avoir la même largeur.

Ici, A2:A5 comporte 4 lignes et B2:D5 comporte également 4 lignes : un résultat MATCH égal à 3 désigne donc bien la troisième ligne de données. Si vous recherchez accidentellement A1:A5, qui inclut un en-tête, les positions sont décalées d’une ligne et vous obtenez la mauvaise cellule.

Testez votre compréhension du modèle de recherche à deux dimensions.

Vous avez appris le modèle de recherche à deux dimensions :

• INDEX renvoie la valeur située à une position donnée de ligne et de colonne dans un bloc.
• Un premier MATCH trouve la ligne en recherchant parmi des libellés verticaux.
• Un second MATCH trouve la colonne en recherchant parmi des en-têtes horizontaux.

La formule combinée =INDEX(data, MATCH(row), MATCH(col)) lit deux entrées et renvoie la valeur située à leur intersection. Pour éviter les décalages, veillez à ce que les plages de MATCH aient les mêmes dimensions que le bloc de données.