VLOOKUP ist eine leistungsstarke Funktion. In einem meiner Power Excel-Seminare wird mir jedoch häufig eine Frage von jemandem gestellt, der wissen möchte, ob VLOOKUP alle übereinstimmenden Werte zurückgeben kann. Wie Sie wissen, gibt der VLOOKUP mit False als viertem Argument immer die erste gefundene Übereinstimmung zurück. Im folgenden Screenshot gibt Zelle F2 3623 zurück, da dies die erste Übereinstimmung ist, die für Job J1199 gefunden wurde.
Die Frage ist also, ob VLOOKUP alle Übereinstimmungen zurückgeben kann.
VLOOKUP wird nicht. Aber andere Funktionen können.
Wenn Sie alle Kosten aus Job J1199 zusammenzählen möchten, würden Sie Folgendes verwenden =SUMIFS($B$2:$B$53,$A$2:$A$53,G2):
Wenn Sie Textwerte haben und alle Ergebnisse zu einem einzigen Wert zusammenfügen möchten, können Sie verwenden =TEXTJOIN(", ",TRUE,IF($A$2:$A$53=G2,$C$2:$C$53,"")). Diese Formel funktioniert nur in Office 365 und Excel 2019.
Möglicherweise müssen Sie alle Ergebnisse für einen einzelnen Job in einen neuen Bereich des Arbeitsblatts zurückgeben. Eine brandneue =FILTER(B2:C53,A2:A53=K1,"None Found")Funktion, die 2019 in Office 365 verfügbar sein wird, wird das Problem lösen:
Manchmal möchten die Leute alle VLOOKUPs ausführen und sie summieren. Wenn Ihre Nachschlagetabelle sortiert ist, können Sie verwenden =SUM(LOOKUP(B2:B53,M3:N5)).
Wenn Sie alle VLOOKUPs mit der Exact Match-Version von VLOOKUP summieren müssen, müssen Sie Zugriff auf dynamische Arrays haben, um sie verwenden zu können =SUM(VLOOKUP(B2:B53,M3:N5,2,TRUE)).
Weitere Informationen zu dynamischen Arrays finden Sie unter Excel Dynamic Arrays direkt auf den Punkt.
Schau Video
Video-Transkript
Lernen Sie Excel aus, Podcast-Episode 2247: Können Sie alle VLookUp-Werte zurückgeben?
Hallo. Willkommen zurück im Netcast. Ich bin Bill Jelen. In meinem Seminar in Appleton, Wisconsin, sind letzte Woche zwei Fragen aufgetaucht - beide bezogen sich. Sie sagten, hey, wie geben wir alle VLOOKUPs zurück, okay? In diesem Fall hat J1199 eine Reihe von Übereinstimmungen und sie möchten sie alle zurückgeben. Meine erste Frage, wann immer mich jemand fragt, ist, was möchten Sie mit den Übereinstimmungen tun? Sind es Zahlen, die Sie addieren möchten, oder ist es Text, den Sie verketten möchten? Und es ist lustig. Die beiden Fragen im selben Seminar, eine Person wollte sie addieren und die andere Person wollte die Ergebnisse verketten.
Schauen wir uns also beide an. Suchen Sie in der YouTube-Beschreibung nach einem Inhaltsverzeichnis, in dem Sie zum anderen springen können, wenn Sie das Ergebnis des Textes sehen möchten.
Okay, als erstes, wenn wir sie alle addieren wollen, werden wir überhaupt keinen VLOOKUP verwenden. Wir werden eine Funktion namens SUMIF oder SUMIFS verwenden, die alles summiert, was zu diesem Element passt. Also, SUMIFS. Hier sind die numerischen Werte, die wir summieren möchten, und ich drücke F4, um das zu sperren. Auf diese Weise zeigt es beim Kopieren immer wieder auf denselben Bereich, und dann möchten wir überprüfen, ob die JOB-Nummer in Spalte A, wieder F4, = auf den Wert links von uns ist - in diesem Fall E2 - und wenn wir das nach unten kopieren, sehen wir die GESAMT für jeden Artikel. (SUMIFS ($ B $ 2: $ B $ 53, $ A $ 2: $ A $ 53, E2))
Lassen Sie uns hier nur einen kleinen Check machen. J1199. Die Summe ist 25365. In Ordnung. Das funktioniert also. Wenn es sich um Zahlen handelt und Sie alle Zahlen abrufen und addieren möchten, wechseln Sie zu SUMIF oder SUMIFS. Wenn es sich jedoch um Text handelt, ist diese Funktion in Office 365 im Februar 2017 neu. Wenn Sie also über Excel 2016 verfügen oder Excel 2013 oder Excel 2010 oder eines dieser älteren, Sie werden diese Funktion nicht haben. Es ist eine Funktion namens TEXTJOIN. TEXTJOIN. Dies ist eine weitere Funktion von (Joe McDade - 01:50), der uns 2018 bei Ignite all diese großartigen dynamischen Array-Formeln gebracht hat, und Joe hat dafür gesorgt, dass TEXTJOIN mit Arrays funktioniert, was wirklich großartig ist.
Das Trennzeichen hier wird also SPACE sein, auf jeden Fall LEER ignorieren. Wir möchten hier LEER ignorieren, weil wir in diesem nächsten Teil, der IF-Anweisung, eine Menge Leergut generieren werden. WENN dieses Element über A2, F4 = zu dieser JOB-Nummer hier ist, dann möchte ich das entsprechende Element aus Spalte C, F4, andernfalls möchte ich "" so. Schließen Sie diese IF-Anweisung. Schließen Sie den TEXTJOIN. Muss ich STRG + UMSCHALT + EINGABETASTE drücken? Nein, tue ich nicht. Es bringt mir alle Produkte, die so zusammenpassen, okay? Also, alle VLOOKUPs zurückgeben, wenn wir sie summieren wollen, ja, wenn wir sie verketten wollen, ja. (= TEXTJOIN (",", True, IF ($ A $ 2: $ A $ 53 = E2, $ C $ 2: $ C $ 53, ""))
Okay, jetzt gibt es hier eine andere Möglichkeit, wenn Leute mich fragen, ob sie alle VLOOKUPs zurückgeben können. Es könnte ein Problem sein, bei dem wir jede dieser Kosten hier nachschlagen und die HANDHABUNGSKOSTEN herausfinden und sie dann alle zusammenfassen möchten. Ich möchte hier kein VLOOKUP und hier kein VLOOKUP und hier kein VLOOKUP und hier kein VLOOKUP platzieren. Ich möchte sie nur zusammen machen und in diesem Fall werden wir die SUMME-Funktion und dann die alte, alte LOOKUP-Funktion verwenden. LOOKUP sagt, dass wir alle diese Werte in Spalte B nachschlagen werden. Ich brauche hier kein F4, weil ich es nirgendwo kopiere. ,. Hier ist unsere Nachschlagetabelle. ), schließe die SUMME, und sie geht aus und macht jeden einzelnen VLOOKUP und summiert sie dann alle so. (= SUMME (LOOKUP (B2: B53, K3: L5)))
Nun, hey. Alle diese Themen sind mein Buch LIV: Die 54 größten Tipps aller Zeiten. Klicken Sie auf das i in der oberen rechten Ecke, um mehr zu erfahren.
Die Frage ist also, ob Sie alle VLOOKUPs zurückgeben können. Nun, irgendwie, aber nicht wirklich mit VLOOKUP. Wir werden entweder SUMIF, TEXTJOIN oder SUM oder LOOKUP verwenden, um das Problem zu lösen.
Nun, hey. Ich möchte mich bei Ihnen für Ihren Besuch bedanken. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Weißt du, okay, ich habe eine Woche lang über diese dynamischen Arrays gesprochen. Ich wollte ein Video machen, in dem ich keine dynamischen Arrays angesprochen habe, weil ich weiß, dass viele Leute sie noch nicht haben, aber hier sind wir. Es ist das Outtake. Sie wissen, diese sind nicht alphabetisch. Dies wäre viel besser, wenn wir sie sortieren könnten, und wenn Sie zufällig die neuen dynamischen Arrays haben, könnten Sie diese in die SORT-Funktion senden, so sortieren und ENTER drücken, und jetzt werden die Ergebnisse so sortiert.
Sie wissen, auch diese Formel könnte mit den dynamischen Arrays besser werden. Für die Suche müssen Sie TRUE verwenden. Was ist, wenn Sie a, FALSE verwenden möchten? Wir könnten das in ein VLOOKUP ändern, den ganzen Text in dieser Tabelle nachschlagen, 2 ,. In diesem Fall werde ich TRUE verwenden, in einem anderen Fall könnten Sie FALSE verwenden. STEUERUNG + UMSCHALT + EINGABETASTE. Nein, es wird einfach funktionieren, okay? (= SUMME (VLOOKUP (B2: B53, K3: L5,2, True)))
Dynamische Arrays, die Anfang 2019 herauskommen, werden so viele Probleme lösen.
Danke, dass du hier durch das Outtake rumgehangen hast. Wir sehen uns beim nächsten Mal für einen weiteren Netcast von.
Laden Sie die Excel-Datei herunter
So laden Sie die Excel-Datei herunter: can-you-return-all-vlookup-values.xlsx
Wenn jemand fragt "Kann VLOOKUP alle Übereinstimmungen zurückgeben, lautet die Antwort Nein. Es gibt jedoch viele andere Funktionen, die im Wesentlichen dasselbe tun können.
Excel-Gedanke des Tages
Ich habe meine Excel Master-Freunde um Rat zu Excel gebeten. Der heutige Gedanke zum Nachdenken:
"Normalisieren Sie Ihre Daten so, wie andere ihre Daten für Sie normalisieren würden."
Kevin Lehrbass
