RECHERCHEV est une fonction puissante. Mais je reçois souvent une question dans l'un de mes séminaires Power Excel de quelqu'un qui veut savoir si VLOOKUP peut renvoyer toutes les valeurs correspondantes. Comme vous le savez, la RECHERCHEV avec False comme quatrième argument renverra toujours la première correspondance trouvée. Dans la capture d'écran suivante, la cellule F2 renvoie 3623 car il s'agit de la première correspondance trouvée pour le travail J1199.
La question, alors, VLOOKUP peut-il renvoyer tous les matchs?
VLOOKUP ne le fera pas. Mais d'autres fonctions le peuvent.
Si vous souhaitez totaliser tous les coûts de la tâche J1199, vous devez utiliser =SUMIFS($B$2:$B$53,$A$2:$A$53,G2),
Si vous avez des valeurs textuelles et souhaitez joindre tous les résultats en une seule valeur, vous pouvez utiliser =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Cette formule ne fonctionne que dans Office 365 et Excel 2019.
Ou bien, vous devrez peut-être renvoyer tous les résultats d'un seul travail dans une nouvelle plage de la feuille de calcul. Une toute nouvelle =FILTER(B2:C53,A2:A53=K1,"None Found")fonction qui arrivera dans Office 365 en 2019 résoudra le problème:
Parfois, les gens veulent effectuer tous les RECHERCHEV et les additionner. Si votre table de recherche est triée, vous pouvez utiliser =SUM(LOOKUP(B2:B53,M3:N5)).
Si vous devez additionner toutes les VLOOKUP avec la version Exact Match de VLOOKUP, vous devrez avoir accès à Dynamic Arrays pour pouvoir l'utiliser =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Pour en savoir plus sur les tableaux dynamiques, consultez Tableaux dynamiques Excel directement au point.
Regarder la vidéo
Transcription vidéo
Learn Excel From, Podcast Episode 2247: Pouvez-vous renvoyer toutes les valeurs VLookUp?
Hey. Bienvenue à nouveau sur le netcast. Je suis Bill Jelen. Deux questions ont été soulevées lors de mon séminaire à Appleton, Wisconsin la semaine dernière - toutes deux liées. Ils ont dit, hé, comment pouvons-nous retourner tous les VLOOKUP, d'accord? Dans ce cas, comme J1199 a un tas de matchs et ils veulent tous les renvoyer, et ma première question chaque fois que quelqu'un me le demande est, eh bien, que voulez-vous faire avec les matchs? S'agit-il de nombres que vous souhaitez additionner ou s'agit-il de texte que vous souhaitez concaténer? Et c'est drôle. Les deux questions dans le même séminaire, une personne voulait les additionner et l'autre voulait concaténer les résultats.
Jetons donc un coup d'œil à ces deux éléments. Vérifiez dans la description YouTube pour une table des matières où vous pouvez passer à l'autre si vous voulez voir le résultat du texte.
D'accord, donc, première chose, si nous voulons tous les ajouter, nous n'utiliserons pas du tout une RECHERCHEV. Nous allons utiliser une fonction appelée SUMIF ou SUMIFS qui va additionner tout ce qui correspond à cet élément. Alors, SUMIFS. Voici les valeurs numériques que nous voulons additionner et j'appuie sur F4 pour verrouiller cela. De cette façon, au fur et à mesure que je copie ceci, il continuera à pointer vers la même plage, puis nous voulons aller vérifier et voir si le numéro de JOB dans la colonne A, encore F4 là-bas, est = à la valeur à gauche de nous - dans ce cas E2 - et au fur et à mesure que nous le recopierons, nous verrons le TOTAL pour chaque élément. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Faisons juste un petit contrôle ici. J1199. Le total est de 25 365. Très bien. Donc, ça marche. Si ce sont des nombres et que vous voulez obtenir tous les nombres et les additionner, passez à SUMIF ou SUMIFS, mais s'il s'agit de texte, d'accord, maintenant, cette fonction est nouvelle dans Office 365 en février 2017. Donc, si vous avez Excel 2016 ou Excel 2013 ou Excel 2010 ou l'un de ces plus anciens, vous n'allez pas avoir cette fonction. C'est une fonction appelée TEXTJOIN. TEXTJOIN. C'est une autre fonction de (Joe McDade - 01:50) qui vient de nous apporter toutes ces excellentes formules de tableaux dynamiques chez Ignite en 2018, et Joe s'est assuré que TEXTJOIN fonctionnerait avec des tableaux, ce qui est vraiment génial.
Donc, le délimiteur ici va être, ESPACE, ignorer définitivement VIDE. Nous voulons ignorer EMPTY ici car nous allons générer beaucoup de vides dans cette partie suivante, l'instruction IF. SI cet élément sur un A2, F4, est = à ce numéro de JOB ici, alors je veux l'élément correspondant de la colonne C, F4, sinon, je veux "" comme ça. Fermez cette instruction IF. Fermez le TEXTJOIN. Dois-je appuyer sur Ctrl + Maj + Entrée? Non, je ne. Cela m'apporte tous les produits qui correspondent comme ça, d'accord? Donc, renvoyer tous les RECHERCHEV, si nous voulons les additionner, oui, si nous voulons les concaténer, oui. (= TEXTJOIN (",", True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, "")))
Très bien, maintenant, il y a une autre possibilité ici lorsque les gens me demandent s'ils peuvent renvoyer tous les RECHERCHEV. Il peut s'agir d'un problème où nous voulons rechercher chacun de ces coûts ici et calculer le COÛT DE MANUTENTION, puis les résumer tous. Comme, je ne veux pas mettre une RECHERCHEV ici et une RECHERCHEV ici et une RECHERCHEV ici et une RECHERCHEV ici. Je veux juste les faire ensemble et, dans ce cas, nous allons utiliser la fonction SOMME puis l'ancienne, ancienne fonction LOOKUP. LOOKUP dit que nous allons rechercher toutes ces valeurs dans la colonne B. Je n'ai pas besoin de F4 ici car je ne le copie nulle part. ,. Voici notre table de consultation. ), fermez la SOMME, et il s'éteint et effectue chaque RECHERCHEV individuelle, puis les résume tous comme ça. (= SOMME (RECHERCHE (B2: B53, K3: L5)))
Et bien Salut. Tous ces sujets sont mon livre LIV: Les 54 meilleurs conseils de tous les temps. Cliquez sur ce i dans le coin supérieur droit pour en savoir plus.
La question est donc de savoir si vous pouvez renvoyer tous les RECHERCHES VIDÉO? Eh bien, en quelque sorte, mais pas réellement en utilisant VLOOKUP. Nous allons soit utiliser SUMIF, TEXTJOIN, ou SUM ou LOOKUP pour le résoudre.
Et bien Salut. Je tiens à vous remercier d'être passé. Nous vous reverrons la prochaine fois pour un autre netcast de.
Vous savez, d'accord, je parle de ces tableaux dynamiques depuis une semaine. Je voulais faire une vidéo où je n'ai pas abordé les tableaux dynamiques parce que je sais que beaucoup de gens ne les ont pas encore, mais nous y sommes. C'est la sortie. Vous savez, ce ne sont pas alphabétiques. Ce serait tellement mieux si nous pouvions les trier, et si vous aviez les nouveaux tableaux dynamiques, vous pourriez envoyer ceci dans la fonction TRIER, TRIER comme ceci, et appuyer sur ENTRÉE, et maintenant les résultats seront triés comme ça.
Vous savez, même cette formule pourrait être améliorée avec les tableaux dynamiques. La recherche nécessite que vous utilisiez le, TRUE. Et si vous vouliez utiliser un, FALSE? Nous pourrions changer cela en VLOOKUP, rechercher tout ce texte dans cette table, 2,. Dans ce cas, je vais utiliser TRUE mais, dans un autre cas, vous pourriez utiliser FALSE. CONTROL + SHIFT + ENTER. Non, ça va marcher, d'accord? (= SOMME (RECHERCHEV (B2: B53, K3: L5,2, Vrai)))
Les tableaux dynamiques sortant début 2019 résoudront tant de problèmes.
Merci de passer du temps ici. Nous vous reverrons la prochaine fois pour un autre netcast de.
Télécharger le fichier Excel
Pour télécharger le fichier Excel: can-you-return-all-vlookup-values.xlsx
Quand quelqu'un demande "Est-ce que VLOOKUP peut renvoyer toutes les correspondances, la réponse est non. Mais il existe de nombreuses autres fonctions qui peuvent faire essentiellement la même chose.
Excellente pensée du jour
J'ai demandé à mes amis Excel Master leurs conseils sur Excel. Pensée d'aujourd'hui à méditer:
"Normaliser vos données comme vous voudriez que d'autres normalisent leurs données pour vous"
Kevin Lehrbass
