Excel-Formel: Prüfen, ob eine Zelle einen von mehreren Werten enthält, aber andere Werte ausschließt

Angenommen, es gibt zwei Wertelisten. Sie möchten prüfen, ob die Zelle B3 einen der Werte aus dem Bereich E3:E5 enthält – gleichzeitig aber keinen der Werte aus dem Bereich F3:F4 (siehe Screenshot unten). Dieses Tutorial liefert Ihnen eine Formel, mit der Sie diese Aufgabe in Excel schnell erledigen können, und erklärt deren Argumente.

Allgemeine Formel:

Argumente

Rückgabewert:

Die Formel gibt 1 oder 0 zurück: Sie liefert 1, wenn die Zelle einen der einzuschließenden Werte enthält und gleichzeitig keinen der auszuschließenden Werte; andernfalls liefert sie 0. Dabei werden die Werte 1 und 0 wie die logischen Werte WAHR und FALSCH behandelt.

Funktionsweise dieser Formel

Angenommen, Sie möchten prüfen, ob die Zelle B3 einen der Werte aus dem Bereich E3:E5 enthält – jedoch gleichzeitig alle Werte aus dem Bereich F3:F4 ausschließt. Verwenden Sie hierfür die folgende Formel:

Drücken Sie die Eingabetaste, um das Prüfergebnis zu erhalten.

Erläuterung

Teil 1: (SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0) prüft, ob die Zelle Werte aus E3:E5 enthält

SUCHEN-Funktion: Die SUCHEN-Funktion gibt die Position des ersten Zeichens einer Teilzeichenfolge innerhalb einer anderen zurück. Wird der gesuchte Text gefunden, liefert sie dessen relative Position; andernfalls wird der Fehlerwert #WERT! zurückgegeben. So sucht beispielsweise die Formel SEARCH($E$3:$E$5,B3) jeden Wert aus dem Bereich E3:E5 in Zelle B3 und gibt die jeweilige Position der gefundenen Teilzeichenfolgen in Zelle B3 zurück. Das Ergebnis ist ein Array wie folgt: {1;7;12}.

ISTZAHL-Funktion: Die ISTZAHL-Funktion gibt WAHR zurück, wenn eine Zelle eine Zahl enthält. Daher liefert ISNUMBER(SEARCH($E$3:$E$5,B3)) das Array-Ergebnis {WAHR;WAHR;WAHR}, da die SUCHEN-Funktion drei Zahlen findet.

--ISNUMBER(SEARCH($E$3:$E$5,B3)) wandelt den WAHR-Wert in 1 und den FALSCH-Wert in 0 um. Dadurch verwandelt diese Formel das Array-Ergebnis in {1;1;1}.

SUMMENPRODUKT-Funktion: Multipliziert Bereiche oder summiert Arrays und gibt die Summe der Produkte zurück. SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) liefert 1+1+1=3.

Abschließend wird die linke Formel SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3))) mit 0 verglichen: Ist das Ergebnis größer als 0, lautet das Ergebnis WAHR – andernfalls FALSCH. In diesem Fall wird WAHR zurückgegeben.

Teil 2: (SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0) prüft, ob die Zelle keine Werte aus F3:F4 enthält

Die Formel SUCHEN($F$3:$F$4;B3) sucht jeden Wert aus dem Bereich F3:F4 in Zelle B3 und gibt die Position jeder gefundenen Teilzeichenfolge innerhalb von B3 zurück. Das Ergebnis ist ein Array wie folgt: {#WERT!;#WERT!}.

ISNUMBER(SEARCH($F$3:$F$4,B3)) liefert das Array-Ergebnis {FALSCH;FALSCH}, da die SUCHEN-Funktion keine Übereinstimmungen findet.

--ISNUMBER(SEARCH($F$3:$F$4,B3)) wandelt den WAHR-Wert in 1 und den FALSCH-Wert in 0 um. Dadurch verwandelt diese Formel das Array-Ergebnis in {0;0}.

SUMMENPRODUKT-Funktion: Multipliziert Bereiche oder summiert Arrays und gibt die Summe der Produkte zurück. SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) liefert 0 + 0 = 0.

Abschließend wird die linke Formel SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3))) mit 0 verglichen: Ist das Ergebnis der linken Formel gleich 0, wird WAHR zurückgegeben – andernfalls FALSCH. In diesem Fall lautet das Ergebnis WAHR.

Teil 3: Multiplikation der beiden Formeln

=(SUMPRODUCT(--ISNUMBER(SEARCH($E$3:$E$5,B3)))>0)*(SUMPRODUCT(--ISNUMBER(SEARCH($F$3:$F$4,B3)))=0)

=TRUE*TRUE

=1

In dieser Formel werden 1 und 0 als logische Werte für WAHR und FALSCH interpretiert.

Beispieldatei

Klicken Sie hier, um die Beispieldatei herunterzuladen

Verwandte Formeln

• Prüfen, ob eine Zelle einen bestimmten Text enthält
  Um zu prüfen, ob eine Zelle Texte aus dem Bereich A enthält, aber keine Texte aus dem Bereich B, verwenden Sie eine Matrixformel, die die Funktionen ANZAHL, SUCHEN und UND in Excel kombiniert.
• Prüfen, ob eine Zelle eines von mehreren Elementen enthält
  Dieses Tutorial stellt eine Formel bereit, mit der Sie prüfen können, ob eine Zelle einen von mehreren Werten in Excel enthält, und erläutert die zugehörigen Argumente sowie deren Funktionsweise.
• Prüfen, ob eine Zelle eines der Elemente enthält
  Angenommen, in Excel befindet sich eine Liste von Werten in Spalte E. Sie möchten überprüfen, ob die Zellen in Spalte B mindestens einen der Werte aus Spalte E enthalten, und entsprechend TRUE oder FALSE zurückgeben.
• Prüfen, ob eine Zelle eine Zahl enthält
  Manchmal möchten Sie überprüfen, ob eine Zelle numerische Zeichen enthält. Dieses Tutorial stellt Ihnen eine Formel vor, die TRUE zurückgibt, wenn die Zelle eine Zahl enthält – und andernfalls FALSE.