INDEX-MATCH est devenu un outil plus populaire pour Excel car il résout les limitations de la fonction RECHERCHEV et il est plus facile à utiliser. La fonction INDEX-MATCH dans Excel présente un certain nombre d'avantages par rapport à la fonction VLOOKUP :

1. INDEX et MATCH sont plus flexibles et plus rapides que Vlookup
2. Il est possible d'exécuter une recherche horizontale, une recherche verticale, une recherche bidirectionnelle, une recherche à gauche, une recherche sensible à la casse et même des recherches basées sur plusieurs critères.
3. Dans les données triées, INDEX-MATCH est 30 % plus rapide que VLOOKUP. Cela signifie que dans un ensemble de données plus vaste, une vitesse 30 % plus rapide est plus logique.

Commençons par les concepts détaillés de chaque INDEX et MATCH.

Fonction INDEX

La fonction INDEX dans Excel est très puissante en même temps un outil flexible qui récupère la valeur à un emplacement donné dans une plage. En d'autres termes, il renvoie le contenu d'une cellule, spécifié par le décalage de ligne et de colonne.

Syntaxe:

=INDEX(reference, [row], [column])>

Paramètres:

référence : le tableau de cellules dans lequel le décalage est effectué. Il peut s'agir d'une seule plage ou d'un ensemble de données complet dans un tableau de données. row [facultatif] : le nombre de lignes décalées. Cela signifie que si nous choisissons une plage de référence de tableau comme A1:A5, alors la cellule/le contenu que nous voulons extraire se trouve à quelle distance verticale. Ici, pour la ligne A1 sera 1, pour la ligne A2 = 2, et ainsi de suite. Si nous donnons row = 4 alors il extraira A4. Comme la ligne est facultative, si nous ne spécifions aucun numéro de ligne, elle extrait les lignes entières de la plage de référence. C'est A1 à A5 dans ce cas. column [facultatif] : le nombre de colonnes de décalage. Cela signifie que si nous choisissons une plage de référence de tableau comme A1:B5, alors la cellule/le contenu que nous voulons extraire se trouve à quelle distance horizontale. Ici, pour A1, la ligne sera 1 et la colonne sera 1, pour B1, la ligne sera 1 mais la colonne sera 2 de la même manière pour la ligne A2 = 2 colonnes = 1, pour la ligne B2 = 2 colonne = 2 et ainsi de suite. Si nous donnons ligne = 5 et colonne 2 alors il extraira B5. Comme la colonne est facultative, donc si nous ne spécifions aucun numéro de ligne. puis il extraira la colonne entière dans la plage de référence. Par exemple, si nous donnons la ligne = 2 et la colonne vide, alors il extraira (A2:B2). Si nous ne spécifions pas la ligne et la colonne, la table de référence entière sera extraite (A1: B5).

Tableau de référence : Le tableau suivant servira de tableau de référence pour tous les exemples de la fonction INDEX. La première cellule est en B3 (FOOD) et la dernière cellule diagonale est en F10 (180).

Exemples: Vous trouverez ci-dessous quelques exemples de fonctions d'index.

Cas 1: Aucune ligne ni colonne n'est mentionnée.

Commande d'entrée : =INDEX(B3:C10)

Cas 2 : Seules les lignes sont mentionnées.

Commande d'entrée : =INDEX(B3:C10,2)

Cas 3 : Les lignes et les colonnes sont mentionnées.

Commande d'entrée : =INDEX(B3:D10,4,2)

Cas 4 : Seules les colonnes sont mentionnées.

Commande d'entrée : =INDEX(B3 : D10 , , 2)

Problème avec la fonction INDEX : Le problème avec la fonction INDEX est qu'il est nécessaire de spécifier des lignes et des colonnes pour les données que nous recherchons. Supposons que nous ayons affaire à un ensemble de données d'apprentissage automatique de 10 000 lignes et colonnes, il sera alors très difficile de rechercher et d'extraire les données que nous recherchons. Voici le concept de Match Function, qui identifiera les lignes et les colonnes en fonction d'une certaine condition.

Fonction MATCH

Il récupère la position d'un élément/valeur dans une plage. Il s'agit d'une version moins raffinée d'une VLOOKUP ou d'une HLOOKUP qui renvoie uniquement les informations de localisation et non les données réelles. MATCH n'est pas sensible à la casse et ne se soucie pas de savoir si la plage est horizontale ou verticale.

Syntaxe:

=MATCH(search_key, range, [search_type])>

Paramètres:

search_key : la valeur à rechercher. Par exemple, 42, Chats ou I24. range : le tableau unidimensionnel à rechercher. Il peut s'agir d'une seule ligne ou d'une seule colonne.eg->A1:A10 , A2:D2 etc. search_type [facultatif] : La méthode de recherche. = 1 (par défaut) recherche la plus grande valeur inférieure ou égale à search_key lorsque la plage est triée par ordre croissant.
• = 0 trouve la valeur exacte lorsque la plage n'est pas triée.
• = -1 trouve la plus petite valeur supérieure ou égale à search_key lorsque la plage est triée par ordre décroissant.

Le numéro de ligne ou le numéro de colonne peut être trouvé à l'aide de la fonction match et peut l'utiliser dans la fonction d'index. Ainsi, s'il y a des détails sur un élément, toutes les informations peuvent être extraites sur l'élément en recherchant la ligne/colonne de l'élément à l'aide de match. puis l'imbriquer dans la fonction d'index.

Tableau de référence : Le tableau suivant servira de tableau de référence pour tous les exemples de la fonction MATCH. La première cellule est en B3 (FOOD) et la dernière cellule diagonale est en F10 (180)

Exemples: Vous trouverez ci-dessous quelques exemples de la fonction MATCH.

Cas 1: Type de recherche 0, cela signifie correspondance exacte.

Commande d'entrée : =MATCH(Inde du Sud,C3:C10,0)

Cas 2 : Type de recherche 1 (par défaut).

Commande d'entrée : =MATCH(Inde du Sud,C3:C10)

notes de bas de page de démarque

Cas 3 : Type de recherche -1.

Commande d'entrée : =MATCH(Inde du Sud,C3:C10,-1)

INDEX-MATCH Ensemble

Dans les exemples précédents, les valeurs statiques des lignes et des colonnes ont été fournies dans la fonction INDEX. Supposons qu'il n'y ait aucune connaissance préalable de la position des lignes et des colonnes, alors la position des lignes et des colonnes peut être fournie à l'aide de la fonction MATCH. Il s'agit d'une manière dynamique de rechercher et d'extraire de la valeur.

Syntaxe:

 =INDEX(Reference Table , [Match(SearchKey,Range,Type)/StaticRowPosition],  [Match(SearchKey,Range,Type)/StaticColumnPosition])>

Tableau de référence : Le tableau de référence suivant sera utilisé. La première cellule est en B3 (FOOD) et la dernière cellule diagonale est en F10 (180)

Exemple: Disons que la tâche consiste à trouver le coût du Masala Dosa. On sait que la colonne 3 représente le coût des articles, mais la position dans la ligne de Masala Dosa n'est pas connue. Le problème peut être divisé en deux étapes :

Étape 1: Trouvez la position de Masala Dosa en utilisant la formule :

 =MATCH('Masala Dosa',B3:B10,0)>

Ici, B3 : B10 représente la colonne Alimentaire et 0 signifie la correspondance exacte. Il renverra le numéro de ligne de Masala Dosa.

Étape 2: Trouvez le coût du Masala Dosa. Utilisez la fonction INDEX pour connaître le coût du Masala Dosa. En remplaçant la requête de fonction MATCH ci-dessus dans la fonction INDEX à l'endroit où la position exacte de Masala Dosa est requise, et le numéro de colonne de coût est 3, ce qui est déjà connu.

=INDEX(B3:F10, MATCH('Masala Dosa', B3:B10 , 0) ,3)>

Recherche bidirectionnelle avec INDEX-MATCH ensemble

Dans l'exemple précédent, la position de la colonne de l'attribut Coût était codée en dur. Ce n’était donc pas entièrement dynamique.

Cas 1: Supposons qu'il n'y ait aucune connaissance du numéro de colonne du coût également, alors il peut être obtenu en utilisant la formule :

 =MATCH('Cost',B3:F3,0)>

Ici, B3:F3 représente la colonne d'en-tête.

Cas 2 : Lorsque la valeur de la ligne, ainsi que la valeur de la colonne, sont fournies via la fonction MATCH (sans donner de valeur statique), cela est alors appelé recherche bidirectionnelle. Cela peut être réalisé en utilisant la formule :

 =INDEX(B3:F10, MATCH('Masala Dosa',B3:B10, 0) , MATCH('Cost' ,B3:F3 ,0))>

Recherche à gauche

L'un des principaux avantages d'INDEX et MATCH par rapport à la fonction RECHERCHEV est la possibilité d'effectuer une recherche à gauche. Cela signifie qu'il est possible d'extraire la position de ligne d'un élément à partir de n'importe quel attribut à droite et que la valeur d'un autre attribut à gauche peut être extraite.

Par exemple, disons que nous achetons de la nourriture dont le coût devrait être de 140 Rs. Indirectement, nous disons d’acheter du Biryani. Dans cet exemple, le coût Rs 140/- est connu, il est nécessaire d'extraire la nourriture. Puisque la colonne Coût est placée à droite de la colonne Nourriture. Si RECHERCHEV est appliqué, il ne pourra pas rechercher le côté gauche de la colonne Coût. C'est pourquoi en utilisant RECHERCHEV, il n'est pas possible d'obtenir le nom de l'aliment.

Pour surmonter cet inconvénient, la fonction INDEX-MATCH La recherche à gauche peut être utilisée.
Étape 1: Extrayez d’abord la position de la ligne du coût 140 Rs en utilisant la formule :

 =MATCH(140, D3:D10,0)>

Ici, D3 : D10 représente la colonne Coût où la recherche du numéro de ligne Coût 140 Rs est effectuée.

Étape 2: Après avoir obtenu le numéro de ligne, l'étape suivante consiste à utiliser la fonction INDEX pour extraire le nom de l'aliment à l'aide de la formule :

 =INDEX(B3:B10, MATCH(140, D3:D10,0))>

Ici, B3: B10 représente la colonne alimentaire et 140 est le coût de l'aliment.

Recherche sensible à la casse

En elle-même, la fonction MATCH n'est pas sensible à la casse. Cela signifie que s'il existe un nom d'aliment DHOKLA et que la fonction MATCH est utilisée avec le mot de recherche suivant :

1. Dhokla
2. dhokla
3. DhOkLA

Tout renverra la position de ligne de DHOKLA. Cependant, la fonction EXACT peut être utilisée avec INDEX et MATCH pour effectuer une recherche qui respecte les majuscules et les minuscules.

Fonction exacte : La fonction Excel EXACT compare deux chaînes de texte, en prenant en compte les caractères majuscules et minuscules, et renvoie VRAI si elles sont identiques, et FAUX sinon. EXACT est sensible à la casse.

Exemples:

EXACT(DHOKLA,DHOKLA) : cela renverra True. EXACT(DHOKLA,Dhokla) : cela renverra False. EXACT(DHOKLA,dhokla) : cela renverra False. EXACT(DHOKLA,DhOkLA) : cela renverra False.

Exemple: Disons que la tâche consiste à rechercher le type d'aliment Dhokla mais en tenant compte de la casse. Cela peut être fait en utilisant la formule-

 =INDEX(C3:C10, MATCH(TRUE , EXACT('Dhokla', B3:B10) ,0))>

Ici, la fonction EXACT retournera True si la valeur de la colonne B3:B10 correspond à Dhokla avec la même casse, sinon elle retournera False. Désormais, la fonction MATCH s'appliquera dans la colonne B3 : B10 et recherchera une ligne avec la valeur exacte VRAI. Après cela, la fonction INDEX récupérera la valeur de la colonne C3:C10 (colonne de type d'aliment) sur la ligne renvoyée par la fonction MATCH.

Recherche selon plusieurs critères

L’un des problèmes les plus délicats dans Excel est la recherche basée sur plusieurs critères. En d’autres termes, une recherche qui correspond à plusieurs colonnes en même temps. Dans l'exemple ci-dessous, les fonctions INDEX et MATCH et la logique booléenne sont utilisées pour correspondre sur 3 colonnes.

1. Nourriture.
2. Coût.
3. Quantité.

Pour extraire le coût total.

Exemple: Disons que la tâche consiste à calculer le coût total des pâtes où

Nourriture : Pâtes. Coût : 60. Quantité : 1.

Ainsi dans cet exemple, il y a trois critères pour réaliser un Match. Vous trouverez ci-dessous les étapes de la recherche basée sur plusieurs critères :

Étape 1: Associez d'abord la colonne alimentaire (B3: B10) aux pâtes en utilisant la formule :

 'PASTA' = B3:B10>

Cela convertira les valeurs B3: B10 (colonne alimentaire) en booléennes. C'est vrai là où la nourriture est des pâtes, sinon c'est faux.

Étape 2: Après cela, faites correspondre les critères de coût de la manière suivante :

 60 = D3:D10>

Cela remplacera les valeurs D3:D10 (colonne de coût) par des valeurs booléennes. C'est vrai là où Coût = 60 sinon Faux.

Étape 3: L'étape suivante consiste à faire correspondre le troisième critère qui est Quantité = 1 de la manière suivante :

 1 = E3:E10>

Cela remplacera la colonne E3:E10 (colonne de quantité) par True où Quantité = 1, sinon ce sera False.

Étape 4: Multipliez le résultat des premier, deuxième et troisième critères. Ce sera l'intersection de toutes les conditions et convertira le booléen True/Faux en 1/0.

Étape 5 : Maintenant, le résultat sera une colonne avec 0 et 1. Utilisez ici la fonction MATCH pour trouver le numéro de ligne des colonnes qui contiennent 1. Parce que si une colonne a la valeur 1, cela signifie qu'elle satisfait aux trois critères.

Étape 6 : Après avoir obtenu le numéro de ligne, utilisez la fonction INDEX pour obtenir le coût total de cette ligne.

 =INDEX(F3:F10, MATCH(1, ('Pasta'=B3:B10) * (60=D3:D10) * (1=E3:E10) , 0 ))>

Ici, F3:F10 représente la colonne Coût total.