Direkt zum Inhalt

Rufen Sie den ersten übereinstimmenden Wert in einer Zelle gegen eine Liste ab

Angenommen, Sie haben eine Liste von Schlüsselwörtern, um das erste Schlüsselwort abzurufen, das in einer bestimmten Zelle vorkommt, während die Zelle mehrere andere Werte enthält, müssen Sie ein . verwenden INDEX und PASSEN ZU Formel mit Hilfe von AGGREGAT und SUCHE Funktionen.

Rufen Sie den ersten übereinstimmenden Wert in einer Zelle gegen eine Liste ab 1

Wie rufe ich den ersten übereinstimmenden Wert in einer Zelle gegen eine Liste ab?

Um die erstes übereinstimmendes Keyword in einer Zelle mit der Keywords-Liste Wie in der obigen Tabelle gezeigt, bedeutet dies, dass wir eine enthaltene Übereinstimmung statt einer genauen Übereinstimmung durchführen müssen. Dazu können Sie mit der Funktion SUCHEN die Positionen der in der Zelle in numerischen Zahlen erscheinenden Schlüsselwörter in die Funktion AGGREGIEREN einspeisen. Dann erhält AGGREGATE die kleinste Zahl, indem es seine function_num auf 15 und das ref2-Argument auf 1. Verwenden Sie dann MATCH, um den ersten kleinsten Wert zu finden, und geben Sie die Positionsnummer an INDEX ein, um den Wert an dieser Position abzurufen.

Generische Syntax

=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ Hinweis: Dies ist eine Matrixformel, die Sie mit eingeben müssen Ctrl + Shift + Enter.

  • Schlüsselwort_rng: Der Zellbereich mit Schlüsselwörtern.
  • Suchzelle: Die Zelle, die gesucht werden soll, wenn sie die Schlüsselwörter enthält.

Um die abzurufen Das erste übereinstimmende Keyword wurde in Zelle B5 in der Spalte "Keywords" angezeigt, bitte kopieren oder geben Sie die Formel unten in die Zelle C5 ein und drücken Sie Ctrl + Shift + Enter um das Ergebnis zu erhalten:

=INDEX($ E $ 5: $ E $ 7,VERGLEICH(AGGREGIEREN(15,6,SUCHEN($ E $ 5: $ E $ 7,B5),1),SUCHE($ E $ 5: $ E $ 7,B5),0))

√ Hinweis: Die Dollarzeichen ($) oben stehen für absolute Referenzen, d.h. die Schlüsselwort_rng in der Formel ändert sich nicht, wenn Sie die Formel in andere Zellen verschieben oder kopieren. Es werden jedoch keine Dollarzeichen hinzugefügt lookup_cell weil es dynamisch sein soll. Ziehen Sie nach der Eingabe der Formel den Ausfüllpunkt nach unten, um die Formel auf die unteren Zellen anzuwenden.

Rufen Sie den ersten übereinstimmenden Wert in einer Zelle gegen eine Liste ab 2

Erklärung der Formel

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

  • SUCHEN($E$5:$E$7,B5): Die SEARCH-Funktion gibt die Position jedes Schlüsselworts aus dem Bereich zurück $ E $ 5: $ E $ 7 wenn in numerischen Zahlen gefunden, und die #WERT! Fehler, wenn nicht gefunden. Das Ergebnis wäre in einem Array wie folgt: {15;11;#WERT!}.
  • AGGREGAT(15,6,SUCHEN($E$5:$E$7,B5), 1) = AGGREGAT(15,6,{15;11;#WERT!}, 1): Die AGGREGATE-Funktion mit a function_num von 15 und Option von 6 gibt den kleinsten Wert im Array gemäß der zurück ref2 Argument 1, während Fehlerwerte ignoriert werden. Der Ausschnitt würde also zurückkehren 11.
  • SPIEL(AGGREGAT(15,6,SUCHEN($E$5:$E$7,B5), 1),SUCHEN($E$5:$E$7,B5), 0) = SPIEL(11,{15;11;#WERT!}, 0): Das Übereinstimmungstyp 0 zwingt die MATCH-Funktion, eine genaue Übereinstimmung durchzuführen und die Position von . zurückzugeben 11 im Array {15;11;#WERT!}. Die Funktion wird also zurückkehren 2.
  • INDEX($E$5:$E$7,SPIEL(AGGREGAT(15,6,SUCHEN($E$5:$E$7,B5), 1),SUCHEN($E$5:$E$7,B5), 0)) = INDEX($E$5:$E$7,2): Die INDEX-Funktion gibt dann die 2nd Wert im Bereich $ E $ 5: $ E $ 7, Das ist bbb.

Anmerkung

  • Wenn eine Zelle keine Schlüsselwörter enthält, a #NUM! Fehler wird zurückgegeben.
  • Bei der Formel wird die Groß-/Kleinschreibung nicht beachtet. Um eine Übereinstimmung mit Groß-/Kleinschreibung durchzuführen, können Sie die SUCHEN-Funktion einfach durch ersetzen FINDEN.

Verwandte Funktionen

Excel INDEX-Funktion

Die Excel-INDEX-Funktion gibt den angezeigten Wert basierend auf einer bestimmten Position aus einem Bereich oder einem Array zurück.

Excel MATCH-Funktion

Die Excel MATCH-Funktion sucht in einem Zellbereich nach einem bestimmten Wert und gibt die relative Position des Werts zurück.

Excel-Suchfunktion

In Excel kann Ihnen die SEARCH-Funktion dabei helfen, die Position eines bestimmten Zeichens oder Teilstrings aus der angegebenen Textzeichenfolge zu ermitteln, wie im folgenden Screenshot gezeigt. In diesem Tutorial werde ich die Verwendung dieser SEARCH-Funktion in Excel vorstellen.

Excel AGGREGATE-Funktion

Die Excel-Funktion AGGREGATE gibt ein Aggregat von Berechnungen wie SUM, COUNT, SMALLL usw. mit der Option zurück, Fehler und ausgeblendete Zeilen zu ignorieren.


Verwandte Formeln

Rufen Sie den ersten Listenwert aus einer Zelle ab

Um das erste Schlüsselwort, das in einem Bereich aufgelistet ist, aus einer bestimmten Zelle abzurufen, während die Zelle einen von mehreren Werten enthält, müssen Sie eine ziemlich komplexe Matrixformel mit den Funktionen INDEX, MATCH, ISNUMBER und SEARCH verwenden.

Exakte Übereinstimmung mit INDEX und MATCH

Wenn Sie die in Excel aufgelisteten Informationen zu einem bestimmten Produkt, Film oder einer Person usw. herausfinden möchten, sollten Sie die Kombination aus INDEX- und MATCH-Funktionen sinnvoll nutzen.

Überprüfen Sie, ob eine Zelle einen bestimmten Text enthält

Hier enthält das Lernprogramm einige Formeln, mit denen Sie überprüfen können, ob eine Zelle einen bestimmten Text enthält, und TRUE und FALSE zurückgeben (siehe Abbildung unten). Außerdem werden die Argumente und die Funktionsweise der Formeln erläutert.

Überprüfen Sie, ob die Zelle viele Dinge enthält

Angenommen, in Excel befindet sich in Spalte E eine Liste mit Werten. Sie möchten überprüfen, ob die Zellen in Spalte B alle Werte in Spalte E enthalten, und TRUE oder FALSE zurückgeben (siehe Abbildung unten). Hier in diesem Tutorial finden Sie eine Formel zur Lösung dieser Aufgabe.

Überprüfen Sie, ob die Zelle eines von vielen Dingen enthält

Dieses Lernprogramm enthält eine Formel zum Überprüfen, ob eine Zelle einen von mehreren Werten in Excel enthält, und erläutert die Argumente in der Formel und die Funktionsweise der Formel.


Die besten Tools für die Office-Produktivität

Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben

🤖 Kutools KI-Assistent: Revolutionieren Sie die Datenanalyse basierend auf: Intelligente Ausführung   |  Code generieren  |  Erstellen Sie benutzerdefinierte Formeln  |  Analysieren Sie Daten und erstellen Sie Diagramme  |  Rufen Sie Kutools-Funktionen auf...
Beliebte Funktionen: Suchen, markieren oder identifizieren Sie Duplikate  |  Leere Zeilen löschen  |  Kombinieren Sie Spalten oder Zellen, ohne Daten zu verlieren  |  Runde ohne Formel ...
Super VLookup: Mehrere Kriterien  |  Mehrfachwert  |  Über mehrere Blätter hinweg  |  Unscharfe Suche...
Adv. Dropdown-Liste: Einfache Dropdown-Liste  |  Abhängige Dropdown-Liste  |  Mehrfachauswahl Dropdown-Liste...
Spaltenmanager: Fügen Sie eine bestimmte Anzahl von Spalten hinzu  |  Spalten verschieben  |  Schalten Sie den Sichtbarkeitsstatus ausgeblendeter Spalten um  Spalten vergleichen mit Wählen Sie Gleiche und Unterschiedliche Zellen ...
Ausgewählte Funktionen: Rasterfokus  |  Designansicht  |  Große Formelleiste  |  Arbeitsmappen- und Blattmanager | Ressourcen (Autotext)  |  Datumsauswahl  |  Arbeitsblätter kombinieren  |  Zellen verschlüsseln/entschlüsseln  |  Senden Sie E-Mails nach Liste  |  Superfilter  |  Spezialfilter (Filter fett/kursiv/durchgestrichen...) ...
Top 15 Toolsets12 Text Tools (Text hinzufügen, Zeichen entfernen ...)  |  50+ Chart Typen (Gantt-Diagramm ...)  |  40+ Praktisch Formeln (Berechnen Sie das Alter basierend auf dem Geburtstag ...)  |  19 Einfügen Tools (QR-Code einfügen, Bild aus Pfad einfügen ...)  |  12 Umwandlung (Conversion) Tools (Zahlen zu Wörtern, Currency Conversion ...)  |  7 Zusammenführen & Teilen Tools (Erweiterte Zeilen kombinieren, Teilen Sie Excel-Zellen ...)  |  ... und mehr

Kutools für Excel bietet über 300 Funktionen, Stellen Sie sicher, dass das, was Sie brauchen, nur einen Klick entfernt ist ...

Beschreibung


Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).

  • Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
  • Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
  • Erhöht Ihre Produktivität um 50 % beim Anzeigen und Bearbeiten mehrerer Dokumente.
  • Bringt effiziente Tabs in Office (einschließlich Excel), genau wie Chrome, Edge und Firefox.
Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations