So suchen Sie mehrere Werte in Excel: Schritt für Schritt

Bei der Arbeit mit großen Datenmengen in Microsoft Excel kann es schwierig sein, mehrere Werte zu finden, die mehreren Bedingungen entsprechen. Einige integrierte Funktionen, z. B. SVERWEIS, waren ursprünglich für die Arbeit mit einzelnen Werten konzipiert.

Mehrere Excel-Funktionen können kombiniert werden, um mehrere Werte nachzuschlagen. Dazu gehören VLOOKUP-, INDEX-, MATCH- und IF-Funktionen. Aktuelle Versionen von Excel können dynamische Arrays verwenden, während ältere Versionen Arrayformeln verwenden.

In diesem Artikel erfahren Sie genau, wie Sie diese Funktionen in Formeln verwenden, die mehrere Werte in Ihren Daten finden.

Lass uns gehen!

Inhaltsverzeichnis

• So verwenden Sie SVERWEIS mit mehreren Werten
  • Was sind Array-Formeln?
  • SVERWEIS-Beispiel
  • Neueste Versionen von Excel
  • Ältere Versionen
  • Exakte Übereinstimmung vs. ungefähre Übereinstimmung
  • Mehr über SVERWEIS
• So verwenden Sie INDEX und MATCH zum Nachschlagen mehrerer Werte
  • So verwenden Sie INDEX und MATCH zusammen in Excel 365
  • Arbeiten mit älteren Excel-Versionen
• So verwenden Sie die IF-Funktion, um mehrere Werte zu finden
  • Aktuelle Versionen von Excel
  • Ältere Versionen von Excel
• So verwenden Sie SUMPRODUCT für mehrere Kriterien
• 3 häufige Fehler bei Suchfunktionen
  • 1. #K.A. Fehler
  • 2. #REF! Fehler
  • 3. Zirkuläre Fehler
• So verwenden Sie IFERROR mit Suchfunktionen
• 7 erweiterte Suchtechniken
  • 1. Relative Positionssuche
  • 2. KLEINE Funktion
  • 3. ROW-Funktion
  • 4. FILTER-Funktion
  • 5. Hilfsspalten
  • 6. Dynamische Arrays
  • 7. Power-Abfrage
• Abschließende Gedanken

So verwenden Sie SVERWEIS mit mehreren Werten

Die VLOOKUP- Funktion wird häufig verwendet, um einzelne Werte in einem Datenbereich zu finden. Mit dieser Suchformel können Sie jedoch auch mehrere Übereinstimmungen in Excel nachschlagen.

Standardmäßig wird nur der erste gefundene übereinstimmende Wert zurückgegeben. Sie können die Funktion jedoch so ändern, dass sie mehrere Werte zurückgibt, indem Sie eine Array-Formel verwenden.

Was sind Array-Formeln?

Eine Array-Formel ist eine Formel, die Berechnungen für Datenarrays durchführen kann. Sie wird als Array-Formel bezeichnet, da sie statt eines einzelnen Werts ein Array von Ergebnissen zurückgeben kann.

Beim Erstellen einer Array-Formel sind mehrere einfache Schritte erforderlich:

1. Wählen Sie einen zu durchsuchenden Zellbereich aus.

2. Geben Sie die Formel in die Formelleiste ein.

3. Drücken Sie Strg + Umschalt + Eingabetaste, um den Vorgang abzuschließen.

Die Syntax einer Array-Formel ähnelt der einer regulären Formel, enthält jedoch geschweifte Klammern {} um die Formel. Die geschweiften Klammern zeigen an, dass es sich bei der Formel um eine Array-Formel handelt und dass sie ein Array von Werten zurückgibt.

Die Beispiele in diesem Artikel zeigen Ihnen, wie Sie Arrayformeln richtig verwenden.

SVERWEIS-Beispiel

Unser Beispiel enthält fünf Elemente in Spalte A eines Arbeitsblatts:

• Apfel

• Banane

• Brot

• Karotte

• Kirsche

Die Aufgabe besteht darin, zu prüfen, ob drei bestimmte Früchte in dieser Liste enthalten sind: Apfel, Banane und Kirsche.

Neueste Versionen von Excel

Die SVERWEIS-Syntax hängt davon ab, welche Excel-Version Sie verwenden.

Die neuesten Versionen ( Excel 365 oder Excel 2021 ) unterstützen dynamische Arrays. Mit dieser Funktion können Formeln mehrere Ergebnisse zurückgeben, die in benachbarte Zellen „überlaufen“.

Dies ist die Syntax (das Gleichheitszeichen beginnt die Formel):

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

• lookup_value: Der Wert, den Sie nachschlagen möchten.

• table_array: Die gesamte Datentabelle, die Sie durchsuchen möchten.

• col_index_num: Die Tabellenspaltennummer im table_array, das die Daten enthält, die Sie zurückgeben möchten.

• range_lookup: Optional. Gibt an, ob Sie eine genaue Übereinstimmung oder eine ungefähre Übereinstimmung wünschen.

Unser konkretes Beispiel verwendet diese Formel:

=SVERWEIS({„Apfel“, „Banane“, „Kirsche“},A1:A5,1,FALSE)

Wenn die Formel in Zelle B1 eingegeben wird, werden die Ergebnisse in die Zellen C1 und D1 übertragen. Dieses Bild zeigt das Beispiel in Aktion:

Ältere Versionen

Wenn Sie eine ältere Version von Excel verwenden, die keine dynamischen Arrays unterstützt (z. B. Excel 2019 oder früher), müssen Sie mit einer Array-Formel einen etwas anderen Ansatz verwenden.

Folge diesen Schritten:

1. Klicken Sie auf die Zelle, in der Sie die Ergebnisse für das erste Element anzeigen möchten (z. B. Spalte B).

2. Geben Sie die folgende Formel ein, ohne die Eingabetaste zu drücken: =SVERWEIS({„Apfel“, „Banane“, „Kirsche“}, A1:A5, 1, FALSE)

3. Drücken Sie Strg + Umschalt + Eingabetaste, um dies in eine Array-Formel umzuwandeln.

4. Kopieren Sie Zelle B1 und fügen Sie sie in die Zelle darunter ein (oder verwenden Sie die Füllpunkte).

Wenn Sie Strg + Umschalt + Eingabetaste verwenden, fügt Excel geschweifte Klammern um die Formel ein. Dies weist darauf hin, dass es sich um eine Matrixformel handelt.

Exakte Übereinstimmung vs. ungefähre Übereinstimmung

Standardmäßig verwendet die VLOOKUP-Funktion eine ungefähre Übereinstimmung. Dies bedeutet, dass die nächstgelegene Übereinstimmung zurückgegeben wird, die gefunden werden kann, auch wenn die Zellenwerte nicht genau übereinstimmen.

Wenn Sie eine exakte Übereinstimmung durchführen möchten, können Sie das Argument „range_lookup“ auf FALSE setzen.

Bedenken Sie, dass ungefähre Übereinstimmungen am besten mit geordneten numerischen Werten funktionieren. Dies ist normalerweise nicht geeignet, wenn der Zellenwert Text ist.

Mehr über SVERWEIS

Wenn Sie mehr über diese vielseitige Funktion erfahren möchten, lesen Sie diese Artikel:

• So vergleichen Sie zwei Spalten in Excel mit VLOOKUP

• So finden Sie Unstimmigkeiten mit VLOOKUP

Nachdem Sie nun mit der VLOOKUP-Funktion vertraut sind, werfen wir einen Blick auf zwei weitere Funktionen, die das, was sie tut, auf andere Weise tun können: INDEX und MATCH.

So verwenden Sie INDEX und MATCH zum Nachschlagen mehrerer Werte

Sie können die Funktionen INDEX und MATCH kombinieren, um mehrere Werte in mehreren Zeilen zu finden.

Die INDEX-Funktion in Excel gibt einen Wert oder Verweis auf eine Zelle innerhalb eines angegebenen Bereichs zurück.

=INDEX(array, row_num, [column_num])

• Array: Der Zellbereich, der nach dem Wert durchsucht werden soll.

• row_num: Die Zeilennummer innerhalb des Arrays, aus der ein Wert zurückgegeben werden soll.

• Column_num: (Optional) Die Spaltennummer innerhalb des Arrays, aus der ein Wert zurückgegeben werden soll. Wenn es weggelassen wird, gibt die Funktion die gesamte Zeile zurück.

Die MATCH-Funktion in Excel gibt die Position eines Werts innerhalb eines angegebenen Bereichs zurück.

=MATCH(lookup_value, lookup_array, [match_type])

• Lookup-Wert: Der Wert, nach dem im Lookup-Array gesucht werden soll.

• lookup_array: Der Bereich der Zellen, die nach dem Lookup_Value durchsucht werden sollen.

• match_type: (Optional) Der Typ des durchzuführenden Matchs. Wenn es weggelassen wird, führt die Funktion eine exakte Übereinstimmung durch.

So verwenden Sie INDEX und MATCH zusammen in Excel 365

Um INDEX und MATCH zusammen zum Nachschlagen mehrerer Werte in Excel zu verwenden, müssen Sie eine Array-Formel verwenden.

Wenn man mit den früheren Beispieldaten arbeitet, ist dies die Formel in Excel 365:

=INDEX(A1:A5, MATCH({„Apfel“, „Banane“, „Kirsche“}, A1:A5, 0))

Das obige Beispiel gliedert sich wie folgt:

• INDEX: Dies gibt den Wert einer Zelle in einem angegebenen Bereich basierend auf einer bestimmten Zeilen- und Spaltennummer zurück. In diesem Fall wird der Wert aus dem Bereich A1:A5 zurückgegeben.

• A1:A5: Dies ist der definierte Tabellenbereich, in dem Sie nach dem Wert suchen und aus dem das Ergebnis zurückgegeben wird.

• MATCH: Sucht nach einem bestimmten Element in einem Zellbereich und gibt die relative Position dieses Elements im Bereich zurück.

• {„Apfel“, „Banane“, „Kirsche“}: ​​Dies ist die Array-Konstante, die die Werte enthält, die Sie nachschlagen möchten.

• A1:A5: Dies ist der Bereich, in dem MATCH nach Werten aus der Array-Konstante sucht.

• 0: Dies ist der Match-Typ für die MATCH-Funktion. In diesem Fall ist es 0, was bedeutet, dass Sie nach einer genauen Übereinstimmung und nicht nach einer genauen Übereinstimmung suchen.

 Dieses Bild zeigt die Formel in Aktion:

Arbeiten mit älteren Excel-Versionen

Wenn Sie eine ältere Excel-Datei verwenden, die keine dynamischen Arrays unterstützt (z. B. Excel 2019 oder früher), müssen Sie einen anderen Ansatz verwenden.

Da ältere Versionen das „Überlaufen“ von Formeln in benachbarte Zellen nicht unterstützen, müssen Sie die Verwendung in drei separate Formeln aufteilen.

Folge diesen Schritten:

1. Klicken Sie auf die Zelle, in der Sie das Ergebnis für das erste Element haben möchten (z. B. Zelle B1).

2. Geben Sie die folgende Formel ein:

3. =INDEX(A1:A5, MATCH(„Apfel“, A1:A5, 0))

4. Drücken Sie die Eingabetaste, um die Formel auszuführen.

5. Geben Sie diese Formel in Zelle B2 ein: =INDEX(A1:A5, MATCH(„Banane“, A1:A5, 0))

6. Geben Sie diese Formel in Zelle B3 ein: =INDEX(A1:A5, MATCH(“cherry”, A1:A5, 0))

Dieses Bild zeigt Zellreferenz B3:

INDEX- und MATCH-Funktionen sind nicht die einzigen, mit denen Sie mehrere Werte finden können. Im nächsten Abschnitt schauen wir uns an, wie Sie alternativ die IF-Funktion nutzen können.

So verwenden Sie die IF-Funktion, um mehrere Werte zu finden

Eine andere Möglichkeit, mehrere Zellwerte anhand bestimmter Kriterien zu suchen, besteht darin, die IF-Funktion mit anderen Funktionen zu verwenden.

Mit der IF-Funktion können Sie mehrere Bedingungen testen und je nach Ergebnis dieser Tests unterschiedliche Ergebnisse zurückgeben.

Angenommen, Sie haben eine Tabelle mit Verkaufsdaten mit Spalten für Produkt und Verkäufe. Sie möchten den Verkaufsbetrag für zwei der drei Produkte suchen und addieren.

Aktuelle Versionen von Excel

Um mithilfe der IF-Funktion die Summe der Spalte „Sales“ zu ermitteln, in der das Produkt entweder „Apfel“ oder „Banane“ ist, können Sie eine Array-Formel mit den Funktionen IF, SUM und OR verwenden.

Angenommen, Ihre Daten beginnen in Zelle A1, verwenden Sie die folgende Formel:

=SUMME(IF((A2:A4=“Apfel“)+(A2:A4=“Banane“), B2:B4, 0))

Der Abschnitt (A2:A4=“Apple“)+(A2:A4=“Banana“) erstellt ein Array, das den Wert 1 hat, wenn die Zelle im Bereich A2:A4 „Apple“ oder „Banana“ enthält, andernfalls 0 .

Die IF-Anweisung prüft jedes Element des Array-Arguments. Wenn der Wert 1 ist (dh das Produkt ist entweder „Apfel“ oder „Banane“), wird der entsprechende Wert in der Spalte „Verkäufe“ (Bereich B2:B4) übernommen. andernfalls dauert es 0.

Die SUM-Funktion addiert die Werte aus der IF-Funktion und summiert so effektiv die Verkaufswerte für „Apfel“ und „Banane“.

Dieses Bild zeigt die Formel in Aktion im Suchbereich:

Ältere Versionen von Excel

In Excel 2019 oder früher müssen Sie eine Array-Formel verwenden. Folge diesen Schritten:

1. Geben Sie die Formel ein, drücken Sie jedoch nicht die Eingabetaste.

2. Drücken Sie Strg + Umschalt + Eingabetaste, um daraus eine Array-Formel zu machen.

Excel fügt der Formel geschweifte Klammern {} hinzu, um anzuzeigen, dass es sich um eine Array-Formel handelt.

Als Nächstes schauen wir uns an, wie Sie mit SUMPRODUCT mehrere Werte basierend auf Ihren Kriterien suchen können. Lass uns gehen!

So verwenden Sie SUMPRODUCT für mehrere Kriterien

Mit der SUMPRODUCT-Funktion können Sie auch mehrere Werte basierend auf mehreren Kriterien suchen.

Da keine Array-Formel erforderlich ist, ist die Syntax unabhängig von der Excel-Version dieselbe.

Unter Verwendung derselben Daten wie im vorherigen Beispiel sieht die Formel folgendermaßen aus:

=SUMMENPRODUKT((A2:A4=“Apfel“)+(A2:A4=“Banane“), B2:B4)

Der Abschnitt (A2:A4=“Apple“)+(A2:A4=“Banana“) erstellt ein Array, das den Wert 1 hat, wenn die Zelle im Bereich A2:A4 „Apple“ oder „Banana“ enthält, andernfalls 0 .

Die SUMPRODUCT-Funktion multipliziert die Elemente des Arrays mit den entsprechenden Elementen in der Spalte „Sales“ (Bereich B2:B4). Anschließend werden die resultierenden Werte addiert, sodass die Verkaufswerte für „Apple“ und „Banane“ effektiv summiert werden.

Die folgende Formel zeigt es in Aktion:

Excel-Funktionen sind erstaunlich, wenn sie wie erwartet funktionieren, aber manchmal können Fehler auftreten. Im nächsten Abschnitt behandeln wir einige der häufigsten Fehler und wie Sie damit umgehen können.

3 häufige Fehler bei Suchfunktionen

Suchfunktionen können manchmal Fehler zurückgeben, deren Fehlerbehebung frustrierend und zeitaufwändig sein kann. Die drei häufigsten Fehler, auf die Sie stoßen, sind:

1. #N/A Fehler

2. #REF! Fehler

3. Zirkuläre Fehler

1. #K.A. Fehler

Der #N/A-Fehler tritt auf, wenn der Suchwert nicht im Sucharray gefunden werden kann.

Es gibt mehrere Gründe, warum dieser Fehler auftreten kann, darunter:

• Der Suchwert ist falsch geschrieben oder falsch.

• Das Sucharray ist nicht in aufsteigender Reihenfolge sortiert.

• Der Suchwert ist nicht im Datensatz enthalten.

Wenn der Suchwert nicht im Datensatz enthalten ist, sind dies nützliche Informationen. Unerfahrene Excel-Benutzer könnten jedoch denken, dass #N/A bedeutet, dass mit der Formel etwas schief gelaufen ist. Im nächsten Abschnitt erfahren Sie, wie Sie dies benutzerfreundlicher gestalten können.

2. #REF! Fehler

Der Schiedsrichter! Ein Fehler tritt auf, wenn das Sucharray oder das Rückgabearray gelöscht oder verschoben wird.

Dieser Fehler kann durch Aktualisieren der Zellreferenzen in der Suchfunktion behoben werden.

3. Zirkuläre Fehler

Wenn Sie Funktionen in komplexen Formeln kombinieren, meldet Excel Ihnen möglicherweise, dass Sie einen Zirkelverweis haben.

Sie können diese leichter untersuchen, indem Sie unseren Leitfaden zum Finden von Zirkelbezügen in Excel verwenden .

So verwenden Sie IFERROR mit Suchfunktionen

Die IFERROR-Funktion ist ein nützliches Werkzeug zur Behandlung von Fehlern in Suchfunktionen. Sie können damit einen Wert oder eine Formel angeben, die zurückgegeben werden soll, wenn die Suchfunktion einen Fehler zurückgibt.

Die Syntax für die IFERROR-Funktion lautet wie folgt:

=IFERROR(value, value_if_error)

· Wert: Wert oder Formel, den/die Sie auswerten möchten.

· Value_if_error: der zurückzugebende Wert oder die Formel, wenn das erste Argument einen Fehler zurückgibt.

Angenommen, Sie haben eine SVERWEIS-Funktion, die mehrere Werte in einer Tabelle nachschlägt. Im Bild unten existiert einer der Werte nicht im gesuchten Datenbereich.

Wie Sie sehen, wird der Fehler #N/A angezeigt, der für unerfahrene Excel-Benutzer verwirrend sein kann.

Stattdessen können Sie IFERROR verwenden, um eine leere Zelle oder eine Meldung mit der Meldung „Nicht gefunden“ mit dieser Syntax anzuzeigen:

=IFERROR(VLOOKUP(lookup_value, table_array, columns_index, FALSE), „Nicht gefunden“)

Wenn in diesem Beispiel die VLOOKUP-Funktion einen Fehler zurückgibt, gibt die IFERROR-Funktion stattdessen die Meldung „Nicht gefunden“ zurück.

Dieses Bild zeigt die Formel in Aktion. Spalte B enthält den fehlenden Wert, während in Spalte C und Spalte D Übereinstimmungen gefunden wurden.

Wir haben bisher viel abgedeckt und Sie sind nun endlich bereit, fortgeschrittenere Suchtechniken zu erlernen, was das Thema des nächsten Abschnitts ist.

7 erweiterte Suchtechniken

Das Nachschlagen mehrerer Werte in Excel kann eine anspruchsvolle Aufgabe sein, insbesondere wenn es um große Datenmengen geht. Bei langsamer Verarbeitung kann es zu Leistungsproblemen kommen.

Es gibt sieben erweiterte Suchtechniken, mit denen Sie den Prozess einfacher und effizienter gestalten können.

• Relative Positionssuche

• KLEINE Funktion

• ROW-Funktion

• FILTER-Funktion

• Hilfsspalten

• Dynamische Arrays

• Power-Abfrage

1. Relative Positionssuche

Eine der einfachsten Möglichkeiten, mehrere Werte in Excel nachzuschlagen, ist die Suche nach relativen Positionen. Dabei müssen die Zeilen- und Spaltenabstände einer bestimmten Zelle angegeben werden, um den gewünschten Wert zu finden.

Wenn Sie beispielsweise über eine Datentabelle verfügen und einen Wert suchen möchten, der zwei Zeilen weiter unten und drei Spalten rechts von einer bestimmten Zelle liegt, können Sie die folgende Formel verwenden:

=OFFSET(Zelle, 2, 3)

2. KLEINE Funktion

Eine weitere nützliche Technik für die Suche nach mehreren Werten ist die Verwendung der SMALL-Funktion. Diese Funktion gibt den n-kleinsten Wert in einem Zellbereich zurück.

Durch die Kombination dieser Funktion mit anderen Suchfunktionen wie INDEX und MATCH können Sie mehrere Werte basierend auf bestimmten Kriterien suchen.

Die folgende Formel sucht beispielsweise nach dem zweitkleinsten Wert in einem Zellbereich, der eine bestimmte Bedingung erfüllt:

=INDEX(Daten, MATCH(SMALL(IF(Kriterien, Bereich), 2), Bereich, 0))

3. ROW-Funktion

Die ROW-Funktion kann auch für die Suche nach mehreren Werten in Excel verwendet werden. Diese Funktion gibt die Zeilennummer einer bestimmten Zelle zurück, die zum Referenzieren von Zellen in einer Datentabelle verwendet werden kann.

Die folgende Formel sucht beispielsweise einen Wert in einer Tabelle basierend auf einem eindeutigen Bezeichner:

=INDEX(Daten, MATCH(unique_identifier, data[unique_identifier], 0), Column_index_number)

4. FILTER-Funktion

Die FILTER-Funktion ist in älteren Excel-Versionen nicht verfügbar.

In Excel 365 können Sie damit einen Zellbereich nach bestimmten Kriterien filtern und nur die Werte zurückgeben, die diese Kriterien erfüllen. Dies ist die Syntax und drei Argumente:

=FILTER(array, include, [if_empty])

• Array: Die spezifischen Daten, die Sie filtern möchten.

• include: Das Argument include sind die Kriterien oder Bedingungen, die Sie auf das Array anwenden möchten.

• [if_empty] (optional): Der zurückzugebende Wert, wenn keine Zeilen oder Spalten die im Include-Argument angegebenen Kriterien erfüllen.

Die folgende Formel arbeitet beispielsweise mit den Beispieldaten, um Übereinstimmungen für zwei der drei Elemente in der ersten Spalte zu finden und ihre entsprechenden Werte in der zweiten Spalte zu summieren.

=SUMME(FILTER(B2:B4, (A2:A4=“Apfel“)+(A2:A4=“Banane“)))

Dieses Bild zeigt, wie viele Zeilen übereinstimmen und wie hoch die Summe der verschiedenen Werte ist:

5. Hilfsspalten

Sie können eine Hilfsspalte verwenden, um mehrere Felder innerhalb einer Funktion wie SVERWEIS zusammenzufügen.

Angenommen, Sie arbeiten mit Vor- und Nachnamen in einem separaten Blatt. Sie können sie in einer Hilfsspalte verketten, auf die in der endgültigen Formel verwiesen wird.

6. Dynamische Arrays

Wie Sie in früheren Beispielen erfahren haben, können Microsoft 365-Benutzer dynamische Arrays für die Suche nach mehreren Werten in Excel nutzen.

Mit dynamischen Arrays können Sie mehrere Werte aus einer einzigen Formel zurückgeben, was die Suche nach großen Datenmengen erleichtert.

7. Power-Abfrage

Power Query ist ein leistungsstarkes Tool in Excel, mit dem Werte basierend auf mehreren Kriterien zurückgegeben werden können.

Dieses Video findet beispielsweise unordentliche Daten in einer Tabelle und bereinigt sie.

Abschließende Gedanken

Damit ist unser tiefer Einblick in die Kunst, mehrere Werte in Excel nachzuschlagen, abgeschlossen! Sobald Sie die VLOOKUP-, INDEX-, MATCH- und Array-Formeln beherrschen, werden Sie sich durch komplexe Datensätze bewegen wie ein heißes Messer durch Butter.

Der Schlüssel liegt darin, die Syntax und die Logik hinter jeder Formel zu verstehen. Bedenken Sie, dass diese Formeln komplex sein können. Daher ist es wichtig, dass Sie sich Zeit nehmen und Ihre Formeln gründlich testen, bevor Sie sich bei der Analyse wichtiger Daten darauf verlassen.

Excel ist ein leistungsstarkes Tool, aber es ist nur so gut wie der Benutzer. Verfeinern Sie diese Fähigkeiten also weiter, experimentieren Sie weiter, und Sie werden bald der Meister der Suche nach mehreren Werten sein. Rechnen Sie bis zum nächsten Mal weiter mit diesen Zahlen und lassen Sie Excel die harte Arbeit für Sie erledigen!