Comment combiner INDEX et MATCH (2 fois) pour une recherche Excel puissante ?

Comprendre le problème de la recherche Excel

Excel est un outil formidable pour organiser et analyser des données. Cependant, extraire des informations spécifiques d'un tableau peut parfois s'avérer complexe. La fonction RECHERCHEV est souvent la solution par défaut, mais elle présente des inconvénients :

• Elle ne fonctionne que si la colonne de recherche est la première colonne du tableau.
• Elle est sensible aux insertions et suppressions de colonnes.
• Elle peut être difficile à comprendre et à déboguer pour les utilisateurs novices.

C'est là que la combinaison INDEX et MATCH entre en jeu. Elle offre une solution plus robuste et flexible.

INDEX et MATCH : les bases

Avant de plonger dans INDEX MATCH MATCH, il est crucial de comprendre le fonctionnement de chaque fonction individuellement.

La fonction INDEX

La fonction INDEX renvoie la valeur d'une cellule dans une plage spécifiée, en fonction de son numéro de ligne et de son numéro de colonne. Sa syntaxe est la suivante :

=INDEX(plage, numéro_de_ligne, [numéro_de_colonne])

• plage : La plage de cellules dans laquelle vous souhaitez rechercher.
• numéro_de_ligne : Le numéro de la ligne dans la plage à partir de laquelle vous souhaitez renvoyer une valeur.
• [numéro_de_colonne] : (Facultatif) Le numéro de la colonne dans la plage à partir de laquelle vous souhaitez renvoyer une valeur. Si omis, INDEX renvoie toute la ligne.

Exemple :

Imaginez un tableau avec des noms de produits dans la colonne A et leurs prix dans la colonne B. Si vous voulez récupérer le prix du produit situé à la 3ème ligne, vous utiliserez la formule :

=INDEX(B1:B10, 3)

Cela renverra la valeur de la cellule B3.

La fonction MATCH

La fonction MATCH renvoie la position relative d'une valeur spécifiée dans une plage. En d'autres termes, elle indique à quelle ligne ou colonne se trouve cette valeur. Sa syntaxe est la suivante :

=MATCH(valeur_recherchée, plage_de_recherche, [type_de_correspondance])

• valeur_recherchée : La valeur que vous recherchez.
• plage_de_recherche : La plage de cellules dans laquelle vous effectuez la recherche.
• [type_de_correspondance] : (Facultatif) Spécifie le type de correspondance. 0 pour une correspondance exacte (le plus courant), 1 pour la plus grande valeur inférieure ou égale à valeur_recherchée (la plage doit être triée en ordre croissant), -1 pour la plus petite valeur supérieure ou égale à valeur_recherchée (la plage doit être triée en ordre décroissant).

Exemple :

Si vous voulez trouver la position du produit "Pomme" dans la plage A1:A10, vous utiliserez la formule :

=MATCH("Pomme", A1:A10, 0)

Si "Pomme" se trouve à la 5ème ligne, la formule renverra 5.

La puissance de INDEX MATCH

En combinant INDEX et MATCH, vous pouvez effectuer des recherches flexibles et dynamiques. L'idée est d'utiliser MATCH pour trouver le numéro de ligne ou de colonne correspondant à votre critère de recherche, puis d'utiliser ce numéro dans la fonction INDEX pour récupérer la valeur correspondante.

Exemple :

Reprenons l'exemple des produits et des prix. Pour trouver le prix du produit "Pomme", vous utiliserez la formule :

=INDEX(B1:B10, MATCH("Pomme", A1:A10, 0))

Ici, MATCH("Pomme", A1:A10, 0) renvoie la position de "Pomme" dans la colonne A (par exemple, 5). Ensuite, INDEX(B1:B10, 5) renvoie la valeur de la cellule B5, qui est le prix de la "Pomme".

INDEX MATCH MATCH : La recherche bidirectionnelle

Maintenant, passons à la combinaison INDEX MATCH MATCH. Cette technique est particulièrement utile lorsque vous avez besoin de rechercher une valeur en fonction de deux critères : un critère de ligne et un critère de colonne. Imaginez un tableau avec les ventes de différents produits dans différentes régions. Vous voulez trouver les ventes d'un produit spécifique dans une région spécifique.

Comment ça marche ?

L'idée est d'utiliser un MATCH pour trouver le numéro de ligne correspondant au premier critère (par exemple, le produit) et un autre MATCH pour trouver le numéro de colonne correspondant au deuxième critère (par exemple, la région). Ensuite, vous utilisez ces deux numéros dans la fonction INDEX pour récupérer la valeur correspondante.

La syntaxe

La formule INDEX MATCH MATCH a la syntaxe suivante :

=INDEX(plage_de_données, MATCH(critère_ligne, plage_critères_ligne, 0), MATCH(critère_colonne, plage_critères_colonne, 0))

• plage_de_données : La plage de cellules contenant les données que vous voulez extraire.
• critère_ligne : La valeur que vous utilisez pour identifier la ligne (par exemple, le nom du produit).
• plage_critères_ligne : La plage de cellules contenant les valeurs des critères de ligne (par exemple, la liste des noms de produits).
• critère_colonne : La valeur que vous utilisez pour identifier la colonne (par exemple, le nom de la région).
• plage_critères_colonne : La plage de cellules contenant les valeurs des critères de colonne (par exemple, la liste des noms de régions).

Exemple concret

Supposons que vous ayez le tableau suivant :

Vous voulez trouver les ventes de "Banane" dans la "Région Sud".

• plage_de_données : B2:E4 (les données de ventes)
• critère_ligne : "Banane"
• plage_critères_ligne : A2:A4 (la liste des produits)
• critère_colonne : "Région Sud"
• plage_critères_colonne : B1:E1 (la liste des régions)

La formule serait donc :

=INDEX(B2:E4, MATCH("Banane", A2:A4, 0), MATCH("Région Sud", B1:E1, 0))

Cette formule renverra la valeur 130, qui correspond aux ventes de "Banane" dans la "Région Sud".

Avantages de INDEX MATCH MATCH

• Flexibilité : Vous pouvez rechercher des données dans n'importe quelle direction (de gauche à droite, de haut en bas, ou même en diagonale).
• Robustesse : La formule est moins sensible aux insertions et suppressions de colonnes ou de lignes que RECHERCHEV.
• Lisibilité : Bien qu'elle puisse sembler complexe au premier abord, la formule est en réalité assez facile à comprendre une fois que vous maîtrisez les fonctions INDEX et MATCH.

Conseils et astuces pour INDEX MATCH MATCH

• Nommer les plages : Pour rendre vos formules plus lisibles et plus faciles à maintenir, vous pouvez nommer les plages de données, les plages de critères de ligne et les plages de critères de colonne. Par exemple, vous pouvez nommer B2:E4 "Ventes", A2:A4 "Produits" et B1:E1 "Regions". La formule deviendrait alors : =INDEX(Ventes, MATCH("Banane", Produits, 0), MATCH("Région Sud", Regions, 0))
• Utiliser des références de cellules : Au lieu d'écrire directement les critères de recherche dans la formule, utilisez des références de cellules. Cela vous permettra de modifier facilement les critères de recherche sans avoir à modifier la formule elle-même. Par exemple, si vous mettez "Banane" dans la cellule G1 et "Région Sud" dans la cellule G2, la formule deviendrait : =INDEX(B2:E4, MATCH(G1, A2:A4, 0), MATCH(G2, B1:E1, 0))
• Gérer les erreurs : Si la valeur recherchée n'est pas trouvée, la fonction MATCH renverra une erreur #N/A. Vous pouvez utiliser la fonction SIERREUR pour gérer cette erreur et afficher un message plus convivial. Par exemple : =SIERREUR(INDEX(B2:E4, MATCH("Banane", A2:A4, 0), MATCH("Région Sud", B1:E1, 0)), "Valeur non trouvée")
• Tableaux structurés: L'utilisation de tableaux structurés Excel (via l'onglet Insertion -> Tableau) simplifie grandement la gestion des plages de données et rend les formules encore plus robustes. Les noms de colonnes deviennent alors des références directes, évitant l'utilisation des plages classiques (A1:B10).

Erreurs courantes à éviter

• Oublier le 0 dans MATCH : L'argument type_de_correspondance dans la fonction MATCH est crucial. Si vous voulez une correspondance exacte, assurez-vous de mettre 0. Si vous l'omettez, Excel utilisera par défaut 1, ce qui peut donner des résultats inattendus si vos données ne sont pas triées.
• Inverser les plages : Assurez-vous que la plage de données dans INDEX correspond bien aux plages de critères dans MATCH. Une erreur courante est d'utiliser une plage de données trop petite ou trop grande, ce qui entraînera des erreurs.
• Confondre les critères de ligne et de colonne : Vérifiez que vous utilisez le bon critère et la bonne plage pour la ligne et la colonne.

Alternatives à INDEX MATCH MATCH

Bien que INDEX MATCH MATCH soit une technique puissante, il existe d'autres méthodes pour effectuer des recherches bidirectionnelles dans Excel :

• RECHERCHEX : La fonction RECHERCHEX est une fonction plus récente qui combine les fonctionnalités de RECHERCHEV et RECHERCHEH et offre une plus grande flexibilité. Elle est disponible dans les versions récentes d'Excel (Office 365 et versions ultérieures).
• RECHERCHEH et RECHERCHEV combinées : Vous pouvez combiner RECHERCHEH (recherche horizontale) et RECHERCHEV (recherche verticale) pour effectuer des recherches bidirectionnelles, mais cette méthode est généralement moins élégante et moins flexible que INDEX MATCH MATCH.

Conclusion

La combinaison des fonctions INDEX et MATCH, et en particulier INDEX MATCH MATCH, est un outil puissant pour effectuer des recherches complexes dans Excel. Bien qu'elle puisse sembler intimidante au premier abord, elle offre une flexibilité et une robustesse supérieures à la fonction RECHERCHEV. En comprenant les bases de chaque fonction et en suivant les conseils et astuces présentés dans cet article, vous serez en mesure de maîtriser cette technique et de l'appliquer à vos propres feuilles de calcul. N'hésitez pas à expérimenter et à adapter les exemples à vos besoins spécifiques. Avec un peu de pratique, INDEX MATCH MATCH deviendra un atout précieux dans votre arsenal Excel.