Note: The other languages of the website are Google-translated. Back to English

Wie kann ich mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien in Excel zurückgeben?

Normalerweise ist es für die meisten von uns einfach, einen bestimmten Wert zu suchen und den passenden Artikel zurückzugeben, indem sie die VLOOKUP-Funktion verwenden. Haben Sie jemals versucht, mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien zurückzugeben, wie im folgenden Screenshot gezeigt? In diesem Artikel werde ich einige Formeln zur Lösung dieser komplexen Aufgabe in Excel vorstellen.

Geben Sie mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien mit Arrayformeln zurück


Geben Sie mehrere übereinstimmende Werte basierend auf einem oder mehreren Kriterien mit Arrayformeln zurück

Zum Beispiel möchte ich alle Namen extrahieren, die 28 Jahre alt sind und aus den USA stammen. Bitte wenden Sie die folgende Formel an:

1. Kopieren Sie die folgende Formel oder geben Sie sie in eine leere Zelle ein, in der Sie das Ergebnis suchen möchten:

=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Hinweis: In der obigen Formel B2: B11 ist die Spalte, aus der der übereinstimmende Wert zurückgegeben wird; F2, C2: C11 sind die erste Bedingung und die Spaltendaten, die die erste Bedingung enthalten; G2, D2: D11 Sind die zweite Bedingung und die Spaltendaten, die diese Bedingung enthalten, ändern Sie sie bitte nach Ihren Wünschen.

2. Dann drücken Strg + Umschalt + Enter Geben Sie die Tasten ein, um das erste übereinstimmende Ergebnis zu erhalten. Wählen Sie dann die erste Formelzelle aus und ziehen Sie den Füllpunkt nach unten in die Zellen, bis der Fehlerwert angezeigt wird. Jetzt werden alle übereinstimmenden Werte wie im folgenden Screenshot angezeigt:

Tipps: Wenn Sie nur alle übereinstimmenden Werte basierend auf einer Bedingung zurückgeben müssen, wenden Sie bitte die folgende Array-Formel an:

=IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11)-ROW($D$2)+1), ROW(1:1))),"" )


Weitere relative Artikel:

  • Rückgabe mehrerer Suchwerte in einer durch Kommas getrennten Zelle
  • In Excel können wir die VLOOKUP-Funktion anwenden, um den ersten übereinstimmenden Wert aus einer Tabellenzelle zurückzugeben. Manchmal müssen wir jedoch alle übereinstimmenden Werte extrahieren und dann durch ein bestimmtes Trennzeichen wie Komma, Bindestrich usw. in ein einzelnes trennen Zelle wie im folgenden Screenshot gezeigt. Wie können wir in Excel mehrere Suchwerte in einer durch Kommas getrennten Zelle abrufen und zurückgeben?
  • In Google Sheet mehrere übereinstimmende Werte gleichzeitig anzeigen und zurückgeben
  • Die normale Vlookup-Funktion in Google Sheet kann Ihnen helfen, den ersten übereinstimmenden Wert basierend auf bestimmten Daten zu finden und zurückzugeben. Manchmal müssen Sie jedoch alle übereinstimmenden Werte wie im folgenden Screenshot anzeigen und zurückgeben. Haben Sie gute und einfache Möglichkeiten, diese Aufgabe in Google Sheet zu lösen?
  • 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.
  • Mehrere Werte vertikal in Excel anzeigen und zurückgeben
  • 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.
  • Übereinstimmende Daten zwischen zwei Werten in Excel anzeigen und zurückgeben
  • In Excel können wir die normale Vlookup-Funktion anwenden, um den entsprechenden Wert basierend auf bestimmten Daten zu erhalten. Manchmal möchten wir jedoch den übereinstimmenden Wert zwischen zwei Werten anzeigen und zurückgeben, wie im folgenden Screenshot gezeigt. Wie können Sie mit dieser Aufgabe in Excel umgehen?

 


  • 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

 

Kommentare (25)
Noch keine Bewertungen. Bewerten Sie als Erster!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Ich habe genau diese Formel ausprobiert; 100% kopiert. Das einzige, was ich geändert habe, waren die Daten, die abgeglichen und zurückgegeben wurden. Wenn ich diese Formel verwende, sagt Excel: "Sie haben zu viele Argumente für diese Funktion eingegeben).=INDEX('Volumenbericht 2020'!$B$3:$B$100,SMALL(IF(COUNTIF($A$1,'2020 Volume Report'!$A$3:$A$100)*COUNTIF($A$3,'2020 Volume Report'!$D$3:$D$100),ROW('2020 Volume Report'!$A$3:$G$100)- MIN(ROW('2020 Volume Report'!$A$3:$G$100))+1,"0"),ROW(A1),COLUMN(A1))
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, könntest du deine Daten und Formelfehler hier als Screesnhot geben?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, wie kann ich es für horizontale Bedingungen verwenden?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Was ist die "0" nach dem +1 in der Formel? Das ist nicht in dem Beispiel eins.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, ich hatte die gleiche Formel ausprobiert. Ich bekomme ein Ergebnis, aber wenn ich CSE gebe, gibt es keine Mehrfachantworten
Dieser Kommentar wurde vom Moderator auf der Website minimiert

Dieser Kommentar wurde vom Moderator auf der Website minimiert
In Bezug auf die Rückgabe mehrerer übereinstimmender Werte basierend auf einem oder mehreren Kriterien mit Array-Formeln: Warum funktioniert es nicht, wenn ich die Daten irgendwo anders habe, außer ab A1, obwohl ich alle Zellreferenzen in der Formel aktualisiere?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Welche Änderung der Formel wäre im ersten Beispiel erforderlich, um alle unter 28-Jährigen zurückzugeben?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,

Ich habe mich gefragt, ob es überhaupt möglich ist, ein zweites Kriterium einzugeben, aber aus dem gleichen Bereich wie das erste Kriterium,

Zum Beispiel möchte ich mit dem oben verwendeten Beispiel nach den Namen von Personen aus Amerika und Frankreich suchen. Zelle F3 hätte also Frankreich, Scarlett & Andrew würden auch in die Liste in Spalte G eingetragen

Vielen Dank für die Hilfe im Voraus.
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Nick,

Froh, dass ich Helfen kann. Wenn Sie die Namen von Personen aus Amerika und Frankreich erhalten möchten, rate ich Ihnen, unsere Formel zweimal zu verwenden, um das Ergebnis zu erhalten. Bitte sehen Sie sich den Screenshot an. In F2 und G2 sind die Werte "United States" und "France". Formel anwenden =IFERROR(INDEX($B$2:$B$11, SMALL(IF($F$2=$D$2:$D$11, ROW($D$2:$D$11))-ROW($D$2)+1 ), ROW(1:1))),"" ), um die Ergebnisse für Amerika zu erhalten. Und wenden Sie die Formel an =IFERROR(INDEX($B$2:$B$11, SMALL(IF($G$2=$D$2:$D$11, ROW($D$2:$D$11))-ROW($D$2)+ 1), ROW(1:1))),"" ), um die Ergebnisse für Frankreich zu erhalten. Es ist einfach. Bitte versuchen Sie es.

Mit freundlichen Grüßen
Mandy
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Wenn ich die zweite Formel verwende und nach unten ziehe, wird nichts angezeigt. Das Formelergebnis (fx) besagt, dass es etwas zurückgeben sollte, aber es ist leer. Wie korrigiere ich das?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Alisia,

Froh, dass ich Helfen kann. Ich habe die zweite Formel im Artikel ausprobiert und die Formel nach unten gezogen, der Rest der Ergebnisse wurde zurückgegeben. Ich denke, es kann zwei Gründe für dein Problem geben. Erstens vergessen Sie vielleicht, die Tasten Strg + Umschalt + Eingabe zu drücken, um die Formel einzugeben. Zweitens ist das übereinstimmende Ergebnis nur eins, sodass keine anderen Ergebnisse nicht zurückgegeben werden. Bitte haben Sie einen Scheck.

Mit freundlichen Grüßen
Mandy
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,
Ich habe versucht, die Formel zu verwenden, und sie generiert entweder einen Wert von 0 oder das angehängte Bild
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Milku
Ihr Screenshot zeigte die WPS-Software der MAC-Version, daher bin ich mir nicht sicher, ob unsere Formel verfügbar ist.
Ich habe hier eine Excel-Datei hochgeladen, Sie können versuchen zu sehen, ob sie in Ihrer Umgebung korrekt berechnet werden kann.
Vielen Dank!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,
was wäre nötig, um die erste Formel im folgenden Fall zu erweitern:
Einige IDs sind leer (z. B. Zelle A5 ist leer) und ich möchte eine zusätzliche Bedingung, die nur Zeilen ausgibt, wenn die IDs nicht leer sind. (Die Ausgabe sollte also James und Abdul sein.
Vielen Dank!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Jo,
Um Ihr Problem zu lösen, wenden Sie bitte die folgende Formel an:
=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11)*($A$2:$A$11<>0), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Bitte haben Sie einen ry, hoffe, es kann Ihnen helfen!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,

Wenn ich in Zelle H1 "Name" schreibe und das mit der Formel verknüpfen möchte, wie würde das funktionieren?
Dann könnte ich in Zelle H1 "ID" schreiben und würde automatisch als Ergebnis erhalten: AA1004; DD1009; PP1023 (für die erste Formel)

Vielen Dank im Voraus!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Marie
Entschuldigung, ich kann Ihr erstes Problem nicht verstehen. Können Sie Ihr Problem klarer und detaillierter erklären? Oder Sie können hier einen Screenshot einfügen, um Ihr Problem zu beschreiben.
Bei der zweiten Frage müssen Sie nur den Zellbezug wie folgt ändern:
=INDEX($A$2:$A$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11)*COUNTIF($G$2, $D$2:$D$11), ROW($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))

Denken Sie daran, zu drücken Strg + Umschalt + Enter Schlüssel zusammen.
Bitte versuchen Sie es, ich hoffe, es kann Ihnen helfen!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Heyi, danke für die Formel. Es funktionierte für "feste" Werte / Text als Kriterien. Eines der Kriterien, die ich zu verwenden versuche, ist jedoch eine Bedingung (values ​​<>0 ), aber die beschriebene Formel funktioniert nicht. Wisst ihr, was ich ändern muss, um die Formel anzupassen, damit ich eine Bedingung als eines der Kriterien haben kann, bitte?

Beste,

John
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Markus
Um Ihr Problem zu lösen, lesen Sie bitte diesen Artikel:
https://www.extendoffice.com/documents/excel/6393-excel-vlookup-function.html#b3-2
Es gibt einige detaillierte Erklärungen zu dieser Aufgabe. Sie müssen nur die Criteira in Ihre eigene ändern.
Vielen Dank!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,

Erstmal danke fürs Teilen!

Können Sie bitte eine Lösung für den folgenden Fall bereitstellen:

Ich habe 3 Spalten (A: Enthält Referenzinformationen, B: Enthält zu durchsuchende Informationen, C: Suchergebnis)

Die Bild-URL ist unten angegeben

https://ibb.co/VHCd09K

Spalte A--------------------Spalte B---------------------Spalte C
Dateiname-------------------------Name----------------Dateiname, Dokumentname, Elementname, Name
Geändertes Element------------Element--------------Geändertes Element, Elementname, Element-ID
Spaltenposition
Dokumentname
Elementname
Name
Kategorien
Garantie
Steigung
Element-ID

Was ich brauche, ist, in Spalte A nach einer teilweisen Übereinstimmung mit Zelle B2 (Name) oder B3 (Element) zu suchen und das Ergebnis in einer Zelle zu erhalten.

Danke, Behzad
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Behzad
Vielleicht kann Ihnen die unten stehende benutzerdefinierte Funktion helfen.
Public Function ConcatPartLookUp(rngInput As Range, rngSource As Range, Optional strDelimiter As String, Optional blCaseSensitive)
Dim rng As Range
If strDelimiter = "" Then strDelimiter = ","
If IsMissing(blCaseSensitive) Then
    blCaseSensitive = False
Else
    blCaseSensitive = True
End If
For Each rng In rngSource
    If blCaseSensitive Then
        If InStr(1, rng.Value, rngInput.Value, vbBinaryCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    Else
        If InStr(1, rng.Value, rngInput.Value, vbTextCompare) > 0 Then ConcatPartLookUp = ConcatPartLookUp & strDelimiter & rng.Value
    End If
Next
If Len(ConcatPartLookUp) > 0 Then ConcatPartLookUp = Mid(ConcatPartLookUp, 2, Len(ConcatPartLookUp))
End Function


Nachdem Sie diesen Code kopiert und eingefügt haben, verwenden Sie diese Formel:=ConcatPartLookUp(B2,$A$2:$A$8) um das gewünschte Ergebnis zu erhalten.
Bitte versuchen Sie es, ich hoffe, es kann Ihnen helfen!
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo,

Danke für das Posten dieser Beispiele.
Ich versuche, dies in meinem eigenen Blatt zu implementieren, aber es funktioniert nicht (vielleicht, weil ich eine europäische Version von Excel verwende)?

Ich möchte die Daten der Tage erhalten, an denen ich meine Schichten hatte oder an denen ich "einige" (> 0) Stunden für einen Kunden gearbeitet habe.

In I3 steht also der Name und in J3 der Monat. K3 und L3 sind die Schichten (1 ist gearbeitet) und Stunden (weiß nicht, wie man das einstellt, sollte mehr als null sein)

Meine erwarteten Ergebnisse sind in:
Schichten: I7 und I8
Stunden: J7

Also habe ich im Oktober am 0 mehr als 2 Stunden für 'Person 3' gearbeitet
hatte Schichten für Person 2 am '10' und am 10

Wenn ich '=INDEX($B$2:$B$11, SMALL(IF(COUNTIF($F$2, $C$2:$C$11))*COUNTIF($G$2, $D$2:$D$11), ROW ($A$2:$D$11)-MIN(ROW($A$2:$D$11))+1), ROW(A1)), COLUMN(A1))' in meiner Excel-Tabelle erlaubt es nicht Komma zwischen den verschiedenen Teilen der Formel.
Also muss ich sie in ';' ändern.
Aber wenn ich es versuche, sagt es immer: '#NAME?'

Also kann mir jemand dabei helfen?

Mit freundlichen Grüßen,

Gering
[img]https://drive.google.com/file/d/1iIPQKuj_PNhqWyWlwJ4IQTqGNEd6B9Hw/view?usp=share_link[/img]
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo, wenn es doppelte Werte gibt (z. B. zwei Adams), wie stelle ich sicher, dass es nur 1 Adam und nicht 2 zurückgibt?
Dieser Kommentar wurde vom Moderator auf der Website minimiert
Hallo Bobbi,
Um nur eindeutige übereinstimmende Werte zu extrahieren, sollten Sie die folgende Formel anwenden:
Nachdem Sie die Formel eingefügt haben, drücken Sie bitte Strg + Umschalt + Enter Tasten zusammen, um das richtige Ergebnis zu erhalten.
=IFERROR(INDEX($B$2:$B$5, MATCH(0, COUNTIF(H1:$H$1, $B$2:$B$5)+IF($D$2:$D$5<>$G$2, 1 , 0)+IF($C$2:$C$5<>$F$2, 1, 0), 0)), "")

Bitte versuchen Sie es, ich hoffe, es kann Ihnen helfen!
Es sind noch keine Kommentare vorhanden
Hinterlassen Sie Ihre Kommentare
Als Gast posten
×
Bewerte diese Nachricht:
0   Figuren
Vorgeschlagene Standorte

Folgen Sie uns

Copyright © 2009 - www.extendoffice.com. | Alle Rechte vorbehalten. Unterstützt von ExtendOffice. | Sitemap
Microsoft und das Office-Logo sind Marken oder eingetragene Marken der Microsoft Corporation in den USA und / oder anderen Ländern.
Geschützt durch Sectigo SSL