Manchmal ist es sinnvoll, die Inhalte eines Arbeitsblattes mit denen einer anderen Tabelle zu vergleichen. Kommt ein Wert in der Referenztabelle vor, soll das optisch angezeigt werden.
So heben Sie Zellen hervor, deren Wert in einer Referenztabelle vorkommt
Stellen Sie sich einen Fischhändler vor, der bei Ware die unter Artenschutz steht besondere Vorkehrungen beim Verkauf treffen muss.
In seiner Excel-Datei hat er im ersten Arbeitsblatt die aktuellen Bestellungen aufgelistet. In einer minimalen Variante hat das diese Form:
In einem zweiten Arbeitsblatt führt er eine ständig aktualisierte Liste der gefährdeten Fische. Seine „Tabelle2“ sieht also zum Beispiel so aus:
Wenn nun bei den Bestellungen der Name eines der geschützten Tiere auftaucht, soll eine Markierung auf das Problem hinweisen.
So hilft die Funktion VERGLEICH() beim Nachschlagen
Der Schlüssel zur Lösung der Aufgabe ist die Funktion VERGLEICH(). Sie prüft, ob der Wert des ersten Parameters in einem bestimmten Zellbereich auftaucht, der im zweiten Parameter genannt ist. Falls ja, wird der Index der Zelle im Suchbereich zurückgeliefert. Falls nicht, liefert die Funktion den Fehlerwert #NV zurück.
Für andere Zwecke kann man damit über diesen Index die gefundene Zelle adressieren. Für die Aufgabenstellung genügt es aber, ob es einen Index gibt oder nicht. Wenn ja, kommt der Wert in der Referenztabelle vor.
In der Formel zur Bedingten Formatierung prüfen wir also jede Zelle darauf, ob ihr Wert in der Vergleichstabelle auftaucht.
So bauen Sie die Vergleichstabelle in die Bedingte Formatierung ein
Markieren Sie den Bereich, der die Markierung erhalten soll. Im Beispiel wäre des die Spalte C ab der zweiten Zeile.
Rufen Sie den Menüpunkt Bedingte Formatierung – Neue Regel auf.
Bei Regeltyp wählen Sie den Formel zur Ermittlung der zu formatierenden Zellen verwenden.
Als Formel wählen Sie =VERGLEICH(C2;Tabelle2!A2:A100;0)
Das bedeutet: Die Formatierung wirkt, wenn eine der Zellen im markierten Bereich mit dem Wert der Zellen A2 bis A100 aus dem zweiten Arbeitsblatt übereinstimmt.
Als Formatierung wählen Sie eine auffällige Hervorhebung, wie einen gelben Hintergrund,
Im Beispiel würde sich diese Bedingte Formatierung so auswirken:
Der dritte Parameter von VERGLEICH() wirkt sich auf die Art des Vergleichs aus. Nur mit der Null wird auf echte Übereinstimmung geprüft.
Eine ähnliche Problemstellung finden Sie im Tipp So nutzen Sie in Excel die bedingte Formatierung zusammen mit einer Werteliste. Hier sind allerdings die nachzuschlagenden Daten in die Formel eingebaut. Wenn sich also in den Referenzwerten etwas ändert, muss die Formel für die bedingte Formatierung angepasst werden – bei häufigen Änderungen ist das fehlerträchtig und unbequem.
