Bedingte Formatierung basierend auf XVERWEIS-Ergebnis – Excel
In diesem Tutorial zeigen wir Ihnen, wie Sie in Excel eine bedingte Formatierung basierend auf XVERWEIS anwenden können. Wenn Ihre Excel-Version XVERWEIS nicht unterstützt oder Sie Google Sheets verwenden, lesen Sie, wie Sie stattdessen SVERWEIS verwenden können.
Betrachten wir ein Beispiel, in dem wir eine bedingte Formatierung auf Basis des Ergebnisses einer XVERWEIS-Funktion anwenden möchten.
Formatierung basierend auf einen XVERWEIS-Vergleich
Zunächst wenden wir die bedingte Formatierung auf die Tabelle mit den Namen und Studenten-IDs (Spalte E-H) an, indem wir die Punktzahlen der einzelnen Studenten (Spalte B-C) nachschlagen und die Zellen mit roter Farbe ausfüllen, wenn die Punktzahlen unter 72 liegen. Die Formel wird hier gezeigt, aber im Folgenden werden wir die einzelnen Schritte durchgehen.
=XVERWEIS($F3;$B$3:$B$7;$C$3:$C$7)<72
Schauen wir uns an, wie die obige Formel in der bedingten Formatierung angewendet wird:
Bedingte Formatierung anwenden
- Markieren Sie den Bereich, auf den die bedingte Formatierung angewendet werden soll.
- Gehen Sie in der Menüleiste auf die Hauptregisterkarte Start > Gruppe Formatvorlagen > Bedingte Formatierung > Neue Regel.
- Wählen Sie im Pop-Up-Menü den Regeltyp „Formel zur Ermittlung der zu formatierenden Zellen verwenden“.
- Geben Sie unsere Formel in die Formelleiste ein.
- Klicken Sie auf Formatieren und legen Sie Ihre Formatierungseinstellungen fest. In unserem Fall werden wir eine ROTE Füllfarbe wählen. Klicken Sie auf OK.
- Klicken Sie nun auf OK und dann wird die Regel für die bedingte Formatierung angewendet.
Nachdem wir nun wissen, wie eine bedingte Formatierung angewendet wird, gehen wir die Formel durch.
Es kann hilfreich sein, zunächst die Formel(n) für die bedingte Formatierung in Zellen einzugeben, um zu testen, ob sie wie erwartet funktionieren. Wir werden dies im Folgenden tun.
Die XVERWEIS-Funktion
Zunächst führen wir unseren XVERWEIS durch.
=XVERWEIS($F3;$B$3:$B$7;$C$3:$C$7)
Wir verwenden $-Zeichen, um Zellenbezüge zu sperren. Dies ist wichtig, um eine für alle Zeilen funktionierende Formel zu erstellen.
Logischer Vergleich
Anschließend werden die Ergebnisse mit dem festgelegten Kriterium verglichen.
=H3<72
Bei der bedingten Formatierung wird die Zellenfarbe für alle Zeilen, die WAHR enthalten, geändert.
Kombinieren wir alle Formeln miteinander, erhalten wir unsere ursprüngliche XVERWEIS-Formel:
=XVERWEIS($F3;$B$3:$B$7;$C$3:$C$7)Formatieren, wenn XVERWEIS leer ist
Beachten Sie, dass ein Schüler im vorherigen Beispiel keine Punktzahl hatte. Wir können diese Zellen durch Hinzufügen der ISTLEER-Funktion erkennen und hervorheben:
=ISTLEER(XVERWEIS($F3;$B$3:$B$7;$C$3:$C$7)) 
So wird die Formel ausgewertet:
Formatieren, wenn XVERWEIS in einem Wertebereich liegt
Ein weiteres häufiges Szenario mit XVERWEIS ist die Überprüfung, ob ein Wert innerhalb eines bestimmten Wertebereichs liegt.
Zu diesem Zweck können wir die UND-Funktion zusammen mit XVERWEIS verwenden:
=UND(XVERWEIS($F3;$B$3:$B$7;$C$3:$C$7)>=72;XVERWEIS($F3;$B$3:$B$7;$C$3:$C$7)<=74)
Gehen wir die Formel einmal durch:
Die untere Grenze
Zunächst wird geprüft, ob die Ausgabe von XVERWEIS größer oder gleich einer bestimmten Untergrenze ist (z. B. 72).
=H3>=72
Die obere Grenze
Als Nächstes wird geprüft, ob derselbe Wert kleiner oder gleich der Obergrenze ist (z. B. 74).
=H3<=74
Die UND-Funktion
Schließlich verwenden wir die UND-Funktion, um zu prüfen, ob beide Bedingungen WAHR sind.
=UND(I3;J3)
Die Kombination der obigen Formeln führt zu unserer ursprünglichen Formel:
=UND(XVERWEIS($F3;$B$3:$B$7;$C$3:$C$7)>=72;XVERWEIS($F3;$B$3:$B$7;$C$3:$C$7)<=74)Bedingte Formatierung – Mehrere XVERWEIS-Bedingungen
Sie können problemlos mehrere bedingte Formatierungsregeln hinzufügen:
Bedingte Formatierungsregeln werden in der Reihenfolge (von oben nach unten) angewendet. Wenn Anhalten markiert ist, werden keine weiteren Formatierungsregeln getestet oder angewendet, wenn die entsprechende Bedingung erfüllt ist.
In unserem Beispiel erfüllt der leere XVERWEIS (z. B. ZEILE 6) zwei Bedingungen, die rote (z. B. <72) und die orangefarbene (z. B. ISTLEER). Da die ISTLEER-Regel zuerst angewendet wird und Anhalten markiert ist, wird ZEILE 6 ORANGE hervorgehoben, weil die ISTLEER-Bedingung erfüllt ist und die nachfolgenden Regeln nicht getestet werden.
