Utiliser les fonctions MATCH et INDEX dans Excel

27 août 202223 octobre 2023 AZ

Bienvenue à ce tutoriel Excel, où nous vous expliquerons comment tirer parti des fonctions INDEX et MATCH dans Excel. Ces deux fonctions offrent une alternative à la fonction VLOOKUP.

Introduction aux fonctions INDEX et MATCH dans Excel :

La fonction MATCH permet de trouver la position d’une valeur dans une plage, tandis que la fonction INDEX renvoie la valeur d’une cellule à partir de coordonnées spécifiques. En combinant ces deux fonctions, vous créez une alternative puissante. Elle convient aux scénarios de données non disposées de manière classique.

La recherche verticale dans Excel associe des valeurs d’une colonne à une colonne à droite. Cependant, si vous recherchez des données réparties entre d’autres colonnes et que vous devez effectuer des recherches à gauche de la colonne de référence, une solution alternative consiste à utiliser les fonctions COMPARE et INDEX en combinaison.

INDEX et MATCH dans Excel : une alternative puissante à la fonction VLOOKUP

Les fonctions COMPARE et INDEX font partie de la catégorie des fonctions de recherche et de référence. Cette catégorie inclut la fonction de recherche verticale (VLOOKUP), la plus largement utilisée.

La recherche verticale n’est pas la seule option dans Excel, et peut présenter des défis pour certaines recherches de données. L’utilisation conjointe des fonctions COMPARE et INDEX constitue une alternative très efficace pour rechercher des données dans des tableaux. En effet, même lorsque la valeur recherchée ne se situe pas dans la première colonne, ce qui peut être inattendu.

Ce guide présente la syntaxe de INDEX et MATCH dans Excel, avec des exemples d’utilisation conjointe pour des recherches de données.

Syntaxe de la fonction INDEX :

On utilise la fonction INDEX pour extraire des données d’une plage. Dans ce cas, nous allons l’utiliser pour extraire le nom des produits en fonction de la catégorie.

La syntaxe de la fonction INDEX est la suivante :

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

Dans notre exemple, nous utilisons la fonction COMPARE pour calculer le “numéro_de_ligne” en trouvant la correspondance de la catégorie dans la colonne des noms des produits, avec un “numéro_de_colonne” fixé à 1 pour extraire le nom du produit.

Syntaxe de la fonction COMPARE :

On utilise la fonction Match pour rechercher une valeur dans une plage et renvoyer sa position.

La syntaxe de la fonction COMPARE est la suivante :

`=COMPARE(valeur_cherchée, plage, [type_de_correspondance])`

Valeur : représente ce que vous recherchez, que ce soit une référence de cellule, une date, un nombre ou un texte.

Matrice : spécifie la zone de recherche, qu’il s’agisse d’une ligne ou d’une colonne.

Dans notre exemple, nous utilisons la “valeur_cherchée” pour la catégorie recherchée, la “plage” pour représenter les catégories de produits dans une colonne, et nous fixons le “type_de_correspondance” à 0 pour obtenir une correspondance exacte.

Dans l’exemple illustré dans l’image ci-dessous, nous avons spécifié un code à rechercher dans la cellule H6. Nous avons créé la fonction dans la cellule H7 en utilisant la syntaxe suivante : =MATCH(H9, C4:C13, 0). Ici, H9 représente la valeur à rechercher. C4:C13 est la plage de cellules de la recherche, et 0 indique une recherche exacte.

Le résultat est 5, car “a b16” se situe en cinquième position dans la liste C4:C16.

L’argument “valeur” correspond au contenu que vous souhaitez rechercher, qui est dans ce cas la cellule H6. La “matrice” représente la plage de cellules dans laquelle vous effectuez la recherche, qui est C4:C13. Pour l’argument de “correspondance”, utilisez 0 pour une correspondance exacte, 1 ou -1 pour une correspondance approximative supérieure ou inférieure.

Codes d’erreur de la fonction COMPARER

Si la fonction MATCH ne trouve pas les données recherchées, elle renverra un message d’erreur. Dans l’exemple, #N/A s’affiche car le code n’est pas dans la table matricielle. Ce message d’erreur est généré car nous avons saisi l’argument “Correspondre” à 0, indiquant que la correspondance doit être exacte.

Si vous aviez saisi l’argument 1 ou -1 et que la donnée recherchée n’était pas disponible, la fonction retournerait la position du nombre le plus proche respectivement inférieur ou supérieur à celui recherché.

Pour la bonne gestion et correction des codes d’erreur que renvoient les Fonctions de recherche, veuillez vous référer au tutoriel : Gestion du message d’erreur # N/A

Utilisation pratique : localiser tous les produits de la catégorie “électronique”.

Imaginez avoir une feuille Excel avec des informations sur divers produits, notamment leur nom, leur catégorie, leur prix et leur quantité en stock. Vous souhaitez localiser tous les produits de la catégorie “Électronique”. Voici comment vous pouvez procéder :

Dans la cellule A1, vous entrez “Électronique” comme critère de recherche.

Utilisez la fonction MATCH pour trouver la position de “Électronique” dans la liste des catégories. Supposons que cela renvoie le numéro de ligne 3. La formule pourrait ressembler à ceci : `=MATCH(A1, Plage_des_catégories, 0)`, où “Plage_des_catégories” représente la colonne des catégories de produits.

Maintenant, vous avez la position (dans ce cas, 3) de la catégorie “Électronique” dans la liste. Vous pouvez utiliser la fonction INDEX pour extraire les noms des produits correspondant à cette catégorie. La formule serait : `=INDEX(Plage_des_noms_de_produits, 3, 1)`, où “Plage_des_noms_de_produits” représente la colonne des noms de produits

Cette formule renverra le nom du produit de la catégorie “Électronique” situé à la 3e ligne de la colonne des noms de produits. Cela vous permettra de trouver rapidement tous les produits de cette catégorie, même si la catégorie “Électronique” ne se trouve pas dans la première colonne de votre tableau de données.

Rechercher des valeurs en combinant les fonctions INDEX et MATCH

Comme anticipé, il est tout à fait envisageable d’emboîter la fonction MATCH dans INDEX, ce qui crée une formule de recherche permettant de récupérer des valeurs à la fois à gauche et à droite de la colonne dans laquelle vous recherchez une valeur. Voici deux paramètres syntaxiques pour INDEX et MATCH, alternatives à VLOOKUP.

Comme prévu, il est possible d’imbriquer la fonction MATCH dans INDEX, afin d’obtenir une formule de recherche qui vous permet de renvoyer des valeurs à la fois à gauche et à droite de la colonne dans laquelle rechercher la valeur.
Deux paramètres syntaxiques alternatifs pour INDEX et MATCH sont proposés comme alternatives à VLOOKUP.

Exemples d’application d’INDEX et MATCH pour rechercher des valeurs à gauche

Dans le tableau ci-dessous, l’objectif est de trouver la catégorie de produit correspondant à un code donné. En entrant le code dans la cellule F2, la fonction MATCH identifie que le code “a10” se trouve à la ligne 8. En utilisant INDEX et en imbriquant MATCH, la valeur “Pantalons de snowboard” est extraite de la première colonne à l’intersection de la ligne 8.

Formule : =INDEX(A2:D14, MATCH(F2, B2:B14, 0), 1)

La formule retourne “Pantalons de snowboard” dans la cellule F3.

Simplification de la fonction INDEX

Dans la fonction INDEX, le troisième argument est facultatif. Pour simplifier la formule et éviter de définir ce troisième argument, il est possible de spécifier uniquement la première colonne A2:D14. Cela élimine la nécessité de mentionner le troisième argument (1 ou la première colonne). Cette approche a l’avantage supplémentaire d’améliorer les performances, car seule une colonne est chargée plutôt que la matrice entière de la table.

Ancienne formule : =INDEX(A2:D14, MATCH(F2, B2:B14, 0), 1)

Nouvelle formule : =INDEX(A2:D14, MATCH(F2, B2:B14, 0))

Dans l’exemple, cela permet toujours d’extraire “Pantalons de snowboard” sans préciser le troisième argument.