Si vous lisez des astuces Excel depuis un certain temps, vous avez invariablement trouvé quelqu'un qui parle d'utiliser les fonctions Excel INDEX () & MATCH () au lieu d'Excel VLOOKUP. Pour ma part, il a toujours été trop difficile d'essayer de maîtriser DEUX nouvelles fonctions simultanément. Mais, c'est un truc cool. Donnez-moi cinq minutes et je vais essayer de l'expliquer en anglais simple.
La revue de 30 secondes de RECHERCHEV
Supposons que vous ayez une table des enregistrements des employés. La première colonne est un numéro d'employé et les colonnes restantes contiennent diverses données sur l'employé. Chaque fois que vous avez un numéro d'employé dans la feuille de calcul, vous pouvez utiliser RECHERCHEV pour renvoyer une donnée spécifique sur l'employé. La syntaxe est VLOOKUP (valeur, plage de données, n ° de col., FALSE). Il indique à Excel: "Accédez à la plage de données. Trouvez une ligne qui a (valeur) dans la première colonne de la plage de données. Renvoyez la (col no.) E valeur de cette ligne. Une fois que vous avez compris, c'est très simple et puissant.
Le problème
Un jour, vous avez une situation où vous avez le nom de l'employé, mais vous avez besoin du numéro d'employé. Dans l'image suivante, vous avez un nom en A10 et vous devez trouver le numéro d'employé en B10.
Lorsque le champ clé se trouve à droite des données que vous souhaitez récupérer, RECHERCHEV ne fonctionnera pas. Si seulement la RECHERCHEV acceptait -1 comme numéro de colonne, il n'y aurait aucun problème. Mais ce n'est pas le cas. Une solution courante consiste à insérer temporairement une nouvelle colonne A, à copier la colonne de noms dans la nouvelle colonne A, à la remplir avec RECHERCHEV, à coller les valeurs spéciales, puis à supprimer la colonne temporaire A. Les professionnels d'Excel peuvent probablement effectuer ce déplacement pendant leur sommeil.
Je vais vous suggérer de relever le défi et d'essayer d'utiliser cette méthode en une seule étape. Oui, vous devrez coller la formule sur votre mur pendant quelques semaines, mais vous l'avez fait avec RECHERCHEV il y a longtemps aussi, n'est-ce pas?
Je pense que la raison pour laquelle c'est si difficile est que vous utilisez deux fonctions que vous n'avez probablement jamais utilisées auparavant. Alors, permettez-moi de le décomposer en deux morceaux.
Tout d'abord, il y a la fonction INDEX (). C'est une fonction horriblement nommée. Quand quelqu'un dit «index», cela n'évoque rien dans mon esprit qui soit similaire à ce que fait cette fonction. L'index nécessite trois arguments.
=INDEX(data range, row number, column number)
En anglais, Excel accède à la plage de données et vous renvoie la valeur à l'intersection de la (numéro de ligne) ème ligne et de la (numéro de colonne) ème colonne. Hé, pensez-y - c'est assez simple, non? =INDEX($A$2:$C$6,4,2)vous donnera la valeur en B5.
L' application INDEX () à notre problème, vous pouvez comprendre que pour retourner le nombre d'employés de la gamme, vous pouvez utiliser ceci: =INDEX($A$2:$A$6,?,1). En fait, cette partie semble si triviale qu'elle semble inutile. Mais, lorsque vous remplacez le point d'interrogation par une fonction MATCH (), vous avez la solution.
Voici la syntaxe:
=MATCH(Value, Single-column data range, FALSE)
Il indique à Excel: «Recherchez la plage de données et indiquez-moi le numéro de ligne relatif où vous trouvez une correspondance pour (données). Donc, pour trouver quelle ligne a l'employé dans A10, vous utiliseriez =MATCH(A10,$B$2:$B$6,FALSE). Oui, c'est plus complexe que l'index , mais il devrait être dans l'allée des pros de RECHERCHEV. Si A10 contient "Miller, Bob", alors ce MATCH retournera qu'il est dans la 3ème ligne de la plage B2: B6.
Voilà - la fonction MATCH () indique à la fonction Index dans quelle ligne regarder - vous avez terminé. Prenez la fonction Index, remplacez notre point d'interrogation par la fonction MATCH, et vous pouvez maintenant faire l'équivalent de VLOOKUPs lorsque le champ clé n'est pas dans la colonne de gauche. Voici la fonction à utiliser:
=INDEX($A$2:$A$6,MATCH(A10,$B$2:$B$6,FALSE),1)
La note autocollante sur mon mur le montre en fait sous forme de deux lignes. J'ai d'abord écrit l'explication de MATCH (). Ci-dessous, j'ai écrit l'explication pour INDEX (). J'ai ensuite dessiné une forme d'entonnoir entre les deux pour indiquer que la fonction MATCH () tombe dans le 2ème argument de la fonction INDEX ().
Les premières fois où j'ai eu à faire l'une de celles-ci, j'ai été tenté de claquer une nouvelle colonne temporaire A là-dedans, mais j'ai eu la douleur de le faire à la place. C'est plus rapide et nécessite moins de manipulation. Ainsi, la prochaine fois que vous souhaiteriez pouvoir mettre un nombre négatif dans la fonction RECHERCHEV, essayez cette étrange combinaison d'INDEX et de MATCH pour résoudre vos problèmes.
