XVERWEIS mit mehreren Kriterien und XVERGLEICH in Excel

Mit XVERWEIS können Sie in Excel auch nach mehreren Kriterien suchen. So lässt sich beispielsweise ein Wert nur dann zurückgeben, wenn gleichzeitig der Name, der Ort oder weitere Bedingungen übereinstimmen.

Mit XVERGLEICH können Sie zusätzlich die Position einer gesuchten Spalte dynamisch bestimmen. Dadurch lassen sich flexible Abfragen erstellen, bei denen sowohl die Zeile als auch die Spalte ausgewählt werden.

In dieser Anleitung zeigen wir:

• XVERWEIS mit 2 Kriterien,
• XVERWEIS mit 3 oder mehr Kriterien,
• wie die Mehrfachsuche funktioniert,
• XVERWEIS und XVERGLEICH für eine dynamische Zeilen- und Spaltensuche,
• eine einfachere Variante mit zwei verschachtelten XVERWEIS-Funktionen und
• wie Sie mit FILTER mehrere Treffer zurückgeben.

XVERWEIS mit 2 Kriterien

Ein normaler XVERWEIS sucht normalerweise nach einem einzelnen Kriterium.

Wenn zwei Bedingungen gleichzeitig erfüllt sein müssen, können Sie diese innerhalb des Sucharrays miteinander multiplizieren.

Die Formel lautet beispielsweise:

=XVERWEIS(1;(B3:B12=H2)*(C3:C12=H3);F3:F12;"Nicht gefunden")

Angenommen:

• Spalte B enthält den Namen,
• Spalte C enthält den Ort,
• Spalte F enthält das Gehalt,
• H2 enthält den gesuchten Namen und
• H3 enthält den gesuchten Ort.

Excel gibt den Wert aus Spalte F nur dann zurück, wenn beide Kriterien in derselben Zeile erfüllt sind.

Wie funktioniert XVERWEIS mit mehreren Kriterien?

Der entscheidende Teil der Formel lautet:

(B3:B12=H2)*(C3:C12=H3)

Excel prüft für jede Zeile beide Bedingungen.

Intern entstehen daraus WAHR- und FALSCH-Werte. Bei der Multiplikation werden diese wie 1 und 0 behandelt:

WAHR * WAHR   = 1
WAHR * FALSCH = 0
FALSCH * WAHR = 0
FALSCH * FALSCH = 0

Nur wenn beide Kriterien erfüllt sind, ergibt sich eine 1.

Deshalb sucht XVERWEIS in unserer Formel nach:

XVERWEIS(1; ... )

und gibt den entsprechenden Wert aus dem Ergebnisbereich zurück.

XVERWEIS mit 3 oder mehr Kriterien

Das gleiche Prinzip lässt sich einfach auf weitere Bedingungen erweitern.

Für drei Kriterien:

=XVERWEIS(1;(B3:B12=H2)*(C3:C12=H3)*(D3:D12=H4);F3:F12;"Nicht gefunden")

Hier muss der Datensatz gleichzeitig:

• Kriterium 1 in Spalte B,
• Kriterium 2 in Spalte C und
• Kriterium 3 in Spalte D

erfüllen.

Das Prinzip kann auch auf vier oder mehr Kriterien erweitert werden:

=XVERWEIS(1;(Bereich1=Kriterium1)*(Bereich2=Kriterium2)*(Bereich3=Kriterium3)*(Bereich4=Kriterium4);Ergebnisbereich;"Nicht gefunden")

XVERWEIS und XVERGLEICH kombinieren: Zeile und Spalte dynamisch suchen

Eine andere häufige Anforderung besteht darin, nicht mehrere Kriterien innerhalb einer Zeile zu prüfen, sondern gleichzeitig nach einer Zeile und einer Spalte zu suchen.

Beispielsweise möchten Sie:

• den Mitarbeiter Blake suchen und
• dynamisch auswählen, ob Sie dessen Alter, Umsatz oder Gehalt zurückgeben möchten.

Dafür kann XVERWEIS mit XVERGLEICH kombiniert werden.

Beispiel

Angenommen:

• B3:B12 enthält die Mitarbeiternamen,
• C2:F2 enthält die Spaltenüberschriften,
• C3:F12 enthält die Daten,
• H2 enthält den gesuchten Mitarbeiter und
• H3 enthält die gewünschte Spaltenüberschrift.

Dann kann folgende Formel verwendet werden:

=XVERWEIS(H2;$B$3:$B$12;INDEX($C$3:$F$12;0;XVERGLEICH(H3;$C$2:$F$2;0));"Nicht gefunden")

XVERGLEICH bestimmt dabei zunächst, in welcher Spalte sich die gewünschte Überschrift befindet.

XVERWEIS sucht anschliessend den gewünschten Mitarbeiter und gibt den Wert aus dieser dynamisch bestimmten Spalte zurück.

Wie funktioniert XVERGLEICH?

XVERGLEICH sucht nicht direkt einen Ergebniswert, sondern liefert die Position eines Suchwerts innerhalb eines Bereichs.

Beispiel:

=XVERGLEICH("Gehalt";C2:F2;0)

Angenommen, die Überschriften lauten:

C	D	E	F
Alter	Abteilung	Umsatz	Gehalt

Dann liefert:

=XVERGLEICH("Gehalt";C2:F2;0)

den Wert:

4

weil „Gehalt“ innerhalb des Suchbereichs an vierter Position steht.

Einfacher: Zwei XVERWEIS-Funktionen verschachteln

Für eine reine Zeilen- und Spaltensuche kann die Formel noch einfacher aufgebaut werden.

Sie können zwei XVERWEIS-Funktionen miteinander verschachteln:

=XVERWEIS(H2;$B$3:$B$12;XVERWEIS(H3;$C$2:$F$2;$C$3:$F$12);"Nicht gefunden")

Der innere XVERWEIS sucht zunächst die gewünschte Spalte.

Der äussere XVERWEIS sucht anschliessend die gewünschte Zeile.

Damit kann beispielsweise dynamisch der Wert für:

• Mitarbeiter = Blake
• Spalte = Gehalt

zurückgegeben werden.

Für viele Anwendungen ist diese Variante leichter verständlich als eine Kombination aus INDIREKT, ADRESSE und XVERGLEICH.

Warum wir INDIREKT und ADRESSE hier nicht benötigen

In der bisherigen Lösung wurde die gewünschte Spalte mit einer Kombination aus:

• INDIREKT,
• ADRESSE und
• XVERGLEICH

erzeugt.

Das funktioniert grundsätzlich, macht die Formel jedoch unnötig komplex.

Mit XVERWEIS, XVERGLEICH und INDEX kann Excel direkt mit Zellbereichen arbeiten. Dadurch ist die Formel:

• kürzer,
• leichter verständlich und
• einfacher zu warten.

XVERWEIS mit mehreren Kriterien und Fehlerbehandlung

XVERWEIS besitzt bereits ein eigenes Argument für den Fall, dass kein Treffer gefunden wird.

Beispiel:

=XVERWEIS(1;(B3:B12=H2)*(C3:C12=H3);F3:F12;"Nicht gefunden")

Wenn keine Zeile beide Kriterien erfüllt, zeigt Excel:

Nicht gefunden

anstatt einen #NV-Fehler auszugeben.

Nur den letzten Treffer mit XVERWEIS zurückgeben

Standardmässig sucht XVERWEIS vom ersten zum letzten Eintrag.

Wenn Sie bei mehreren passenden Datensätzen stattdessen den letzten Treffer zurückgeben möchten, können Sie den Suchmodus verwenden:

=XVERWEIS(1;(B3:B100=H2)*(C3:C100=H3);F3:F100;"Nicht gefunden";0;-1)

Der Wert -1 beim letzten Argument bewirkt, dass Excel vom letzten zum ersten Eintrag sucht.

Mehrere Treffer zurückgeben: FILTER statt XVERWEIS

XVERWEIS gibt grundsätzlich einen passenden Treffer zurück.

Wenn mehrere Zeilen die Kriterien erfüllen und Sie alle Treffer zurückgeben möchten, eignet sich die FILTER-Funktion besser.

Beispiel:

=FILTER(F3:F100;(B3:B100=H2)*(C3:C100=H3);"Keine Treffer")

FILTER gibt alle Werte aus Spalte F zurück, bei denen beide Kriterien erfüllt sind.

XVERWEIS oder INDEX und XVERGLEICH?

Anforderung	Empfohlene Lösung
Ein Kriterium	XVERWEIS
Mehrere Kriterien, ein Treffer	XVERWEIS
Dynamische Zeilen- und Spaltensuche	XVERWEIS + XVERGLEICH oder zwei XVERWEIS
Alle passenden Treffer	FILTER
Ältere Excel-Version ohne XVERWEIS	INDEX + VERGLEICH

Wenn Ihre Excel-Version XVERWEIS oder XVERGLEICH nicht unterstützt, finden Sie hier unsere Anleitung zu INDEX und VERGLEICH mit mehreren Kriterien.

Häufige Fehler bei XVERWEIS mit mehreren Kriterien

Die Bereiche sind unterschiedlich gross

Alle Kriterienbereiche und der Ergebnisbereich müssen dieselbe Anzahl Zeilen enthalten.

Richtig:

B3:B100
C3:C100
F3:F100

Problematisch wäre beispielsweise:

B3:B100
C3:C200
F3:F100

Zahl und Text stimmen optisch überein, werden aber nicht gefunden

Excel unterscheidet zwischen Zahlen und Textwerten. Eine als Text gespeicherte Zahl kann deshalb dazu führen, dass XVERWEIS keinen Treffer findet.

Zusätzliche Leerzeichen

Unsichtbare oder zusätzliche Leerzeichen in Namen, Artikelnummern oder anderen Texten können ebenfalls verhindern, dass die Kriterien übereinstimmen.

Mehrere Datensätze erfüllen die Kriterien

XVERWEIS liefert normalerweise einen Treffer zurück. Wenn Sie alle passenden Datensätze benötigen, verwenden Sie FILTER.

Beispieldatei herunterladen

Hier können Sie die bestehende Beispieldatei herunterladen und die Formeln direkt in Excel testen:

Beispiel XVERWEIS und XVERGLEICH – Zeilen und Spalten dynamisch suchen

Häufige Fragen zu XVERWEIS und XVERGLEICH

Kann XVERWEIS mit mehreren Kriterien suchen?

Ja. Mehrere Bedingungen können miteinander multipliziert werden. XVERWEIS sucht anschliessend nach dem Wert 1, der nur entsteht, wenn alle Bedingungen gleichzeitig erfüllt sind.

Wie funktioniert XVERWEIS mit zwei Kriterien?

Eine typische Formel lautet:

=XVERWEIS(1;(B3:B100=H2)*(C3:C100=H3);F3:F100;"Nicht gefunden")

Kann XVERWEIS gleichzeitig nach einer Zeile und einer Spalte suchen?

Ja. Dafür können zwei XVERWEIS-Funktionen verschachtelt oder XVERWEIS mit XVERGLEICH kombiniert werden.

Was ist der Unterschied zwischen XVERWEIS und XVERGLEICH?

XVERWEIS sucht einen Wert und gibt einen zugehörigen Ergebniswert zurück. XVERGLEICH gibt dagegen die relative Position eines gesuchten Werts innerhalb eines Bereichs zurück.

Wie gebe ich mehrere Ergebnisse zurück?

Wenn mehrere Datensätze die Kriterien erfüllen und alle Treffer angezeigt werden sollen, verwenden Sie die FILTER-Funktion.

Was kann ich verwenden, wenn XVERWEIS nicht verfügbar ist?

In älteren Excel-Versionen können Sie INDEX und VERGLEICH verwenden. Auch mehrere Kriterien lassen sich damit kombinieren.

Benötigen Sie Hilfe bei komplexen Excel-Formeln?

Bei umfangreichen Excel-Dateien können Suchformeln mit mehreren Kriterien schnell komplex werden. Häufig lässt sich die Datei durch moderne Excel-Funktionen, Power Query oder eine gezielte Automatisierung vereinfachen.

Wir von exact construct entwickeln und optimieren individuelle Excel-Lösungen für Unternehmen.

Wir unterstützen unter anderem bei:

• komplexen XVERWEIS- und Suchformeln,
• Auswertungen mit mehreren Kriterien,
• Optimierung langsamer Excel-Dateien,
• Power Query und Datenaufbereitung,
• VBA- und Makro-Programmierung sowie
• Automatisierung bestehender Excel-Prozesse.

Haben Sie ein konkretes Excel-Problem? Rufen Sie uns unverbindlich unter +41 52 511 05 25 an oder kontaktieren Sie uns über unser Kontaktformular.

„`