RECHERCHEV est l'une des fonctions les plus utilisées dans Excel. Il recherche une valeur dans une plage et renvoie une valeur correspondante dans un numéro de colonne spécifié.
Maintenant, je suis tombé sur un problème où je devais rechercher une ligne entière et renvoyer les valeurs dans toutes les colonnes de cette ligne (au lieu de renvoyer une seule valeur).
Voici donc ce que je devais faire. Dans l'ensemble de données ci-dessous, j'avais les noms des représentants des ventes et les ventes qu'ils ont réalisées au cours des 4 trimestres de 2012. J'avais une liste déroulante avec leurs noms et je voulais extraire les ventes maximales de ce représentant des ventes au cours de ces quatre trimestres.
Je pourrais proposer 2 façons différentes de le faire - en utilisant INDEX ou VLOOKUP.
Rechercher une ligne/colonne entière à l'aide de la formule INDEX
Voici la formule que j'ai créée pour le faire en utilisant Index
=LARGE(INDICE($B$4:$F$13,MATCH(H3,$B$4:$B$13,0),0),1)
Comment ça fonctionne:
Examinons d'abord la fonction INDEX qui est enveloppée à l'intérieur de la fonction LARGE.
=INDICE($C$4:$F$13,MATCH(H3,$B$4:$B$13,0),0)
Analysons de près les arguments de la fonction INDEX :
- Tableau - $B$4:$F$1
- Numéro de ligne - MATCH(H3,$B$4:$B$13,0)
- Numéro de colonne - 0
Notez que j'ai utilisé le numéro de colonne comme 0.
L'astuce ici est que lorsque vous utilisez le numéro de colonne comme 0, il renvoie toutes les valeurs dans toutes les colonnes. Donc, si je sélectionne John dans la liste déroulante, la formule d'indexation renverrait les 4 valeurs de vente pour John {91064,71690,67574,25427}.
Maintenant, je peux utiliser la fonction Large pour extraire la plus grande valeur
Astuce de pro - Utilisez le numéro de colonne/ligne comme 0 dans la formule d'index pour renvoyer toutes les valeurs dans les colonnes/lignes.
Rechercher une ligne/colonne entière à l'aide de la formule RECHERCHEV
Alors que la formule Index est soignée, propre et robuste, la méthode VLOOKUP est un peu complexe. Cela finit également par rendre la fonction volatile. Cependant, il y a une astuce incroyable que je voudrais partager dans cette section. Voici la formule :
=LARGE(RECHERCHEV(H3,B4:F13, LIGNE(INDIRECT("2:"&COUNTA($B$4:$F$4))), FAUX),1)
Comment ça fonctionne
- ROW(INDIRECT("2:"&COUNTA($B$4:$F$4))) - Cette formule renvoie un tableau {2;3;4;5}. Notez que puisqu'il utilise INDIRECT, cela rend cette formule volatile.
- RECHERCHEV(H3,B4:F13,ROW(INDIRECT("2:"&COUNTA($B$4:$F$4))),FALSE) - Voici la meilleure partie. Lorsque vous les assemblez, cela devient RECHERCHEV(H3,B4:F13,{2;3;4;5},FALSE). Notez maintenant qu'au lieu d'un seul numéro de colonne, je lui ai donné un tableau de numéros de colonne. Et RECHERCHEV recherche docilement les valeurs dans toutes ces colonnes et renvoie un tableau.
- Maintenant, utilisez simplement la fonction LARGE pour extraire la plus grande valeur.
N'oubliez pas d'utiliser Control + Shift + Enter pour utiliser cette formule.
Astuce de pro - Dans RECHERCHEV, au lieu d'utiliser un seul numéro de colonne, si vous utilisez un tableau de numéros de colonne, il renverra un tableau de valeurs de recherche.
