Direkt zum Inhalt

Suchen Sie und geben Sie mehrere Werte basierend auf einem oder mehreren Kriterien zurück

Normalerweise können Sie die Vlookup-Funktion verwenden, um den ersten entsprechenden Wert abzurufen. Manchmal möchten Sie jedoch alle übereinstimmenden Datensätze basierend auf einem bestimmten Kriterium zurückgeben. In diesem Artikel werde ich darüber sprechen, wie alle übereinstimmenden Werte vertikal, horizontal oder in einer einzelnen Zelle angezeigt und zurückgegeben werden.

Suchen Sie nach und geben Sie alle entsprechenden Werte vertikal zurück

Suchen Sie nach und geben Sie alle entsprechenden Werte horizontal zurück

Suchen Sie nach und geben Sie alle entsprechenden Werte in einer Zelle zurück

Suchen Sie nach und geben Sie alle entsprechenden Werte vertikal zurück

Wenden Sie die folgende Array-Formel an, um alle übereinstimmenden Werte basierend auf einem bestimmten Kriterium vertikal zurückzugeben:

1. Geben Sie diese Formel ein oder kopieren Sie sie in eine leere Zelle, in der Sie das Ergebnis ausgeben möchten:

=IFERROR(INDEX($C$2:$C$20, SMALL(IF($E$2=$A$2:$A$20, ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Hinweis: In der obigen Formel C2: C20 Ist die Spalte enthält den übereinstimmenden Datensatz, den Sie zurückgeben möchten? A2: A20 ist die Spalte enthält das Kriterium; und E2 ist das spezifische Kriterium, auf dessen Grundlage Sie Werte zurückgeben möchten. Bitte ändern Sie sie nach Ihren Wünschen.

2. Dann drücken Strg + Umschalt + Enter Drücken Sie die Tasten zusammen, um den ersten Wert zu erhalten, und ziehen Sie dann den Füllpunkt nach unten, um alle entsprechenden Datensätze nach Bedarf abzurufen. Siehe Screenshot:

Tipps:

Um alle übereinstimmenden Werte basierend auf spezifischeren Werten vertikal nachzuschlagen und zurückzugeben, wenden Sie bitte die folgende Formel an und drücken Sie Strg + Umschalt + Enter Schlüssel.

=IFERROR(INDEX($C$2:$C$20, SMALL(IF(1=((--($E$2=$A$2:$A$20))*(--($F$2=$B$2:$B$20))), ROW($A$2:$A$20)-ROW($A$2)+1), ROW(1:1))),"" )

Suchen Sie nach und geben Sie alle entsprechenden Werte horizontal zurück

Wenn Sie möchten, dass die übereinstimmenden Werte in horizontaler Reihenfolge angezeigt werden, kann Ihnen die folgende Array-Formel helfen.

1. Geben Sie diese Formel ein oder kopieren Sie sie in eine leere Zelle, in der Sie das Ergebnis ausgeben möchten:

=IFERROR(INDEX($C$2:$C$20,SMALL(IF($F$1=$A$2:$A$20,ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Hinweis: In der obigen Formel C2: C20 Ist die Spalte enthält den übereinstimmenden Datensatz, den Sie zurückgeben möchten? A2: A20 ist die Spalte enthält das Kriterium; und F1 ist das spezifische Kriterium, auf dessen Grundlage Sie Werte zurückgeben möchten. Bitte ändern Sie sie nach Ihren Wünschen.

2. Dann drücken Strg + Umschalt + Enter Drücken Sie die Tasten zusammen, um den ersten Wert zu erhalten, und ziehen Sie dann den Füllpunkt nach rechts, um alle entsprechenden Datensätze nach Bedarf abzurufen. Siehe Screenshot:

Tipps:

Um alle übereinstimmenden Werte basierend auf spezifischeren Werten horizontal nachzuschlagen und zurückzugeben, wenden Sie bitte die folgende Formel an und drücken Sie Strg + Umschalt + Enter Schlüssel.

=IFERROR(INDEX($C$2:$C$20,SMALL(IF(1=((--($F$1=$A$2:$A$20))*(--($F$2=$B$2:$B$20))),ROW($A$2:$A$20)-ROW($A$2)+1),COLUMN(A1))),"")

Suchen Sie nach und geben Sie alle entsprechenden Werte in einer Zelle zurück

Um alle entsprechenden Werte in einer einzelnen Zelle anzuzeigen und zurückzugeben, sollten Sie die folgende Array-Formel anwenden.

1. Geben Sie die folgende Formel ein oder kopieren Sie sie in eine leere Zelle:

=TEXTJOIN(", ",TRUE,IF($A$2:$A$20=F1,$C$2:$C$20,""))

Hinweis: In der obigen Formel C2: C20 Ist die Spalte enthält den übereinstimmenden Datensatz, den Sie zurückgeben möchten? A2: A20 ist die Spalte enthält das Kriterium; und F1 ist das spezifische Kriterium, auf dessen Grundlage Sie Werte zurückgeben möchten. Bitte ändern Sie sie nach Ihren Wünschen.

2. Dann drücken Strg + Umschalt + Enter Tasten zusammen, um alle übereinstimmenden Werte in einer einzigen Zelle zu erhalten, siehe Screenshot:

Tipps:

Um alle übereinstimmenden Werte basierend auf spezifischeren Werten in einer einzelnen Zelle nachzuschlagen und zurückzugeben, wenden Sie bitte die folgende Formel an und drücken Sie Strg + Umschalt + Enter Schlüssel.

=TEXTJOIN(", ",TRUE,IF(($A$2:$A$20=F1)*($B$2:$B$20=F2),$C$2:$C$20,""))

Hinweis: Diese Formel wurde nur in Excel 2016 und späteren Versionen erfolgreich angewendet. Wenn Sie nicht über Excel 2016 verfügen, sehen Sie sich dies bitte an hier um es runter zu bekommen.

Weitere relative Vlookup-Artikel:

  • Anzeigen und Zurückgeben mehrerer Werte aus der Dropdown-Liste
  • Wie können Sie in Excel mehrere entsprechende Werte aus einer Dropdown-Liste anzeigen und zurückgeben? Wenn Sie also ein Element aus der Dropdown-Liste auswählen, werden alle relativen Werte gleichzeitig angezeigt (siehe Abbildung unten). In diesem Artikel werde ich die Lösung Schritt für Schritt vorstellen.
  • Vlookup zum Zurückgeben von Leer anstelle von 0 oder N / A in Excel
  • Wenn Sie die vlookup-Funktion anwenden, um den entsprechenden Wert zurückzugeben, wird normalerweise, wenn Ihre übereinstimmende Zelle leer ist, 0 zurückgegeben. Wenn Ihr übereinstimmender Wert nicht gefunden wird, wird ein Fehlerwert # N / A angezeigt. Wie können Sie den Wert 0 oder # N / A anzeigen lassen, anstatt eine leere Zelle anzuzeigen?
  • Vlookup, um mehrere Spalten aus der Excel-Tabelle zurückzugeben
  • Im Excel-Arbeitsblatt können Sie die Vlookup-Funktion anwenden, um den übereinstimmenden Wert aus einer Spalte zurückzugeben. Manchmal müssen Sie jedoch übereinstimmende Werte aus mehreren Spalten extrahieren, wie im folgenden Screenshot gezeigt. Wie können Sie mithilfe der Vlookup-Funktion die entsprechenden Werte gleichzeitig aus mehreren Spalten abrufen?
  • Vlookup-Werte in mehreren Arbeitsblättern
  • In Excel können wir die vlookup-Funktion einfach anwenden, um die übereinstimmenden Werte in einer einzelnen Tabelle eines Arbeitsblatts zurückzugeben. Aber haben Sie jemals darüber nachgedacht, wie Sie Werte über mehrere Arbeitsblätter hinweg anzeigen können? Angenommen, ich habe die folgenden drei Arbeitsblätter mit Datenbereich, und jetzt möchte ich einen Teil der entsprechenden Werte basierend auf den Kriterien aus diesen drei Arbeitsblättern erhalten.
  • Super Formelriegel (leicht mehrere Textzeilen und Formeln bearbeiten); Layout lesen (leichtes Lesen und Bearbeiten einer großen Anzahl von Zellen); In gefilterten Bereich einfügen...
  • Zellen / Zeilen / Spalten zusammenführen und Speichern von Daten; Inhalt geteilter Zellen; Kombinieren Sie doppelte Zeilen und Summe / Durchschnitt... doppelte Zellen verhindern; Bereiche vergleichen...
  • Wählen Sie Duplizieren oder Eindeutig Reihen; Wählen Sie Leere Zeilen (alle Zellen sind leer); Super Find und Fuzzy Find in vielen Arbeitsmappen; Zufällige Auswahl ...
  • Exakte Kopie Mehrere Zellen ohne Änderung der Formelreferenz; Referenzen automatisch erstellen zu mehreren Blättern; Aufzählungszeichen einfügen, Kontrollkästchen und mehr ...
  • Lieblingsformeln und schnell einfügen, Bereiche, Diagramme und Bilder; Zellen verschlüsseln mit Passwort; Mailingliste erstellen und E-Mails senden ...
  • Text extrahieren, Text hinzufügen, Nach Position entfernen, Leerzeichen entfernen;; Paging-Zwischensummen erstellen und drucken; Inhalt und Kommentare zwischen Zellen konvertieren...
  • Superfilter (Speichern und Anwenden von Filterschemata auf andere Blätter); Erweiterte Sortierung nach Monat / Woche / Tag, Häufigkeit und mehr; Spezialfilter fett, kursiv ...
  • Kombinieren Sie Arbeitsmappen und Arbeitsblätter;; Tabellen basierend auf Schlüsselspalten zusammenführen; Daten in mehrere Blätter aufteilen; Batch-Konvertierung von xls, xlsx und PDF...
  • Pivot-Tabellengruppierung nach Wochennummer, Wochentag und mehr ... Entsperrte, gesperrte Zellen anzeigen durch verschiedene Farben; Markieren Sie Zellen mit Formel / Name...
kte tab 201905
  • Aktivieren Sie das Bearbeiten und Lesen von Registerkarten in Word, Excel und PowerPoint, Publisher, Access, Visio und Project.
  • Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters und nicht in neuen Fenstern.
  • Steigert Ihre Produktivität um 50 % und reduziert jeden Tag Hunderte von Mausklicks für Sie!
officetab unten
Comments (8)
No ratings yet. Be the first to rate!
Ahmed Abosamaha
Ahmed Abosamaha
This comment was minimized by the moderator on the site
gents

would you please share with me the formula of the three ways but for data in horizontal table.
Reply
0
0
skyyang
skyyang     Ahmed Abosamaha
This comment was minimized by the moderator on the site
Hello, Ahmed,
To solve your problem, please apply the below formulas:
Get the results vertically: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($A$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), ROW(1:1))),"" )
Get the results horizontally: =IFERROR(INDEX($B$2:$K$2, SMALL(IF($D$5=$B$1:$K$1, COLUMN($B$1:$K$1:$K$1)-COLUMN($B$1)+1), COLUMN(A1))),"" )
Ge the results in one cell: =TEXTJOIN(", ",TRUE,IF($B$1:$K$1=J5,$B$2:$K$2,""))
Note: These formulas are array formulas, you should press Ctrl + Shift + Enter keys together to get the correct result.

https://www.extendoffice.com/images/stories/comments/comment-skyyang/doc-vlookup-data.png

Pease try, hope this can help you!
Reply
Report
0
0
Ahmed Abosamaha
Ahmed Abosamaha     skyyang
This comment was minimized by the moderator on the site
thanks for your greet support and quick response
Reply
0
0
itika
itika
This comment was minimized by the moderator on the site
how do u get all rows to fill up like in yr video without pressing ctrl+shift+enter
Reply
0
0
Taia
Taia
This comment was minimized by the moderator on the site
After I extend the data, some of the cells is showing a "0" instead of blank
Reply
0
0
Adam
Adam     Taia
This comment was minimized by the moderator on the site
It means that there is missing data or the wrong type of data in the cell(s) from the column that is being referrenced. For instance, Im working with a bunch of data and I want all ID numbers for workers under a certain supervisor. However, in the table column being referenced with all workers' ID numbers, some cells had the workers last name in it and not their ID numbers. Those exact cells produced 0s when the entire formula was put in, and this is because it was text within a column that is mostly numbers.
Reply
0
0
Win E
Win E
This comment was minimized by the moderator on the site
How can you tweak the formula so that you have multiple outputs in column D? E.g. I want to populate column D with each of the countries, such that all of their respective cities will be returned in E? The only work around I have can see is manually changing $D$2
Reply
0
0
Jeff
Jeff
This comment was minimized by the moderator on the site
Thank you very much, you save lots of work here!





Regarding your formula {=TEXTJOIN(", ",TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,"")},

I changed a little : (=TEXTJOIN(CHAR(10),TRUE,IF($A$2:$A$15=D2,$B$2:$B$15,""))}





Regards,



Jeff
Reply
0
0
There are no comments posted here yet
Please leave your comments in English
Guest
Posting as Guest
×
Rate this post:
Reset
0   Characters
Suggested Locations