Il y a des moments où nos données sont présentées en colonnes et en lignes. Dans ces circonstances, nous pouvons avoir besoin de rechercher à la fois la colonne et la ligne en même temps, comme dans les données ci-dessous.
Mais comment pouvons-nous y parvenir?
En combinant la fonction RECHERCHEV avec la fonction MATCH, nous pouvons effectuer une recherche sur une ligne et une colonne en même temps; ceci est souvent appelé une recherche bidirectionnelle.
La fonction MATCH est très utile; il renvoie la position d’une valeur de recherche dans une plage.
En utilisant nos données d’exemple; nous pouvons trouver le numéro de colonne «Jun» en utilisant la fonction Match.
=MATCH("Jun",B1:M1,0) Le résultat de cette formule est 6, comme dans la gamme B1-M1 «Jun» est le 6ème élément. Si nous recherchions «Nov», cela renverrait 11, car c’est le 11e élément.
Le dernier argument de la fonction MATCH est important. Nous utiliserons 0 car cela fournira une correspondance exacte.
Nous pouvons insérer MATCH dans la fonction VLOOKUP à la place du numéro de colonne.
La fonction RECHERCHEV compte la première colonne comme 1, mais notre fonction MATCH commence à la colonne B, il est donc nécessaire d’ajouter 1 au numéro de colonne pour que RECHERCHEV renvoie la valeur de la colonne correcte.
La formule en B12 est la suivante:
=VLOOKUP(B9,A2:M5,MATCH(B10,B1:M1,0)+1,FALSE) Nous avons vu, dans les articles précédents, qu’il est possible d’utiliser VLOOKUP avec plusieurs critères où les données sont dans deux colonnes ou plus. Mais que faire si nous voulons faire correspondre plusieurs lignes?
L’exemple ci-dessous montre que juillet apparaît deux fois dans nos données, une fois pour 2016 et une fois pour 2017. En faisant de la formule MATCH une formule matricielle, nous pouvons faire correspondre les deux critères de colonne du mois et de l’année ensemble.
La formule dans la cellule B14 est:
{=VLOOKUP(B10,A3:N6,MATCH(B11&"^"&B12,B1:N1&"^"&B2:N2,0)+1,FALSE)} Cette formule commence à paraître un peu compliquée maintenant, alors décomposons-la.
Tout d’abord, il s’agit d’une formule matricielle. Tapez la formule dans Excel sans {}, mais appuyez sur Ctrl + Alt + Entrée pour entrer la formule. Excel ajoutera alors automatiquement le {}.
{=VLOOKUP(B10,A3:N6,MATCH(B11&"^"&B12,B1:N1&"^"&B2:N2,0)+1,FALSE)}
Deuxièmement, regardons simplement le premier argument de la fonction MATCH. Il s’agit simplement de combiner les valeurs de «Jul» et «2016» avec un caractère d’espacement au milieu.
{=VLOOKUP(B10,A3:N6,MATCH(B11&"^"&B12,B1:N1&"^"&B2:N2,0)+1,FALSE)} L’argument suivant de la fonction MATCH crée un tableau temporaire de valeurs avec un caractère d’espacement au milieu. Cela ne fonctionne que parce qu’il s’agit d’une formule matricielle.
{=VLOOKUP(B10,A3:N6,MATCH(B11&"^"&B12,B1:N1&"^"&B2:N2,0)+1,FALSE)} Le tableau temporaire comprendrait les éléments suivants: {«Jul ^ 2016», «Aug ^ 2016», «Sep ^ 2016», «Oct ^ 2016» […all the way up to…] «Jun ^ 2017», «Jul ^ 2017»}
La valeur de recherche dans la fonction MATCH est comparée à ce tableau temporaire. À condition que l’année et le mois correspondent, une valeur sera renvoyée. En modifiant l’année dans la cellule B12, la valeur de N5, plutôt que B5, sera renvoyée. L’image ci-dessous montre le résultat comme 27 au lieu de 23.
Si jamais vous avez besoin de faire correspondre plusieurs lignes de conditions et plusieurs colonnes de conditions ensemble, il est probablement préférable de considérer la formule INDEX / MATCH / MATCH. Comme je ne suis pas sûr qu’il soit possible de pousser la formule VLOOKUP aussi loin.
Téléchargez la feuille de triche Advanced VLOOKUP. Il comprend la plupart des conseils et astuces que nous avons couverts dans cette série, y compris des calculs plus rapides, plusieurs critères, la recherche à gauche et bien plus encore.
Veuillez le télécharger et l’épingler au travail, vous pouvez même le transmettre à vos amis et collègues.
Le téléchargement est disponible pour les abonnés à la newsletter, cliquez sur le bouton ci-dessous pour devenir abonné et télécharger le fichier.
N’oubliez pas:
Si vous avez trouvé cet article utile ou si vous avez une meilleure approche, veuillez laisser un commentaire ci-dessous.
Avez-vous besoin d’aide pour l’adapter à vos besoins?
Je suppose que les exemples de cet article ne correspondaient pas exactement à votre situation. Nous utilisons tous Excel différemment, il est donc impossible d’écrire un article qui répondra aux besoins de chacun. En prenant le temps de comprendre les techniques et principes de cet article (et ailleurs sur ce site) vous devriez pouvoir l’adapter à vos besoins.
Mais si vous avez encore du mal, vous devriez:
sauvegarder
