Exakte vs. ungefähre Übereinstimmung

Das letzte Argument von VLOOKUP, range_lookup, legt fest, wie die Suche ausgeführt wird. Es ist klein, aber wirkungsvoll:

• FALSE (oder 0) bedeutet exakte Übereinstimmung
• TRUE (oder 1) bedeutet ungefähre Übereinstimmung

Die falsche Auswahl gehört zu den häufigsten Fehlern in Tabellenkalkulationen. In dieser Lektion erfahren Sie genau, wann Sie welche Option verwenden sollten.

Eine exakte Übereinstimmung findet einen Wert, der genau übereinstimmt. Ist der Wert nicht vorhanden, gibt VLOOKUP den Fehler #N/A zurück, statt zu raten.

Verwenden Sie FALSE, wenn Sie eindeutige Identifikatoren wie Produktcodes, Mitarbeiter-IDs oder E-Mail-Adressen nachschlagen, bei denen nur eine vollständige Übereinstimmung korrekt ist.

Für eine exakte Übereinstimmung muss die Tabelle nicht sortiert sein. VLOOKUP durchsucht sie, bis der Wert gefunden wird.

Bei unserer Preistabelle (A100 Apple 0.50, B200 Banana 0.30, C300 Cherry 1.20) funktioniert das exakte Nachschlagen eines vorhandenen Codes einwandfrei:

Es wird 1.20 zurückgegeben. Wenn Sie jedoch nach einem nicht vorhandenen Code wie "Z999" suchen, erhalten Sie #N/A. Dieser Fehler ist sogar hilfreich: Er zeigt Ihnen, dass der Eintrag tatsächlich fehlt, statt den falschen Nachbarwert zurückzugeben.

Eine ungefähre Übereinstimmung findet den größten Wert, der kleiner oder gleich Ihrem Nachschlagewert ist. Sie ist für Wertebereiche und Stufen gedacht, nicht für exakte IDs.

Der klassische Anwendungsfall ist eine Stufentabelle: Steuerklassen, Versandstufen, Notengrenzen oder Mengenrabatte, bei denen ein Wert zwischen zwei Schwellenwerten liegt.

Mit TRUE gilt eine wichtige Regel, die in der nächsten Szene behandelt wird.

Damit eine ungefähre Übereinstimmung funktioniert, muss die erste Spalte aufsteigend sortiert sein (vom kleinsten zum größten Wert). VLOOKUP geht die Spalte nach unten durch und bleibt beim letzten Wert stehen, der den Nachschlagewert nicht überschreitet.

Wenn die Spalte nicht sortiert ist, liefert TRUE unvorhersehbare, falsche Ergebnisse – und zwar ohne Fehlermeldung. Wegen dieses unbemerkten Fehlers verzichten viele auf TRUE, sofern sie nicht ausdrücklich eine Stufeneinteilung benötigen.

Angenommen, eine Notentabelle befindet sich in A1:B5 und ist aufsteigend nach der Mindestpunktzahl sortiert:

• 0 = F
• 60 = D
• 70 = C
• 80 = B
• 90 = A

Eine Punktzahl von 76 sollte C zurückgeben, da 76 in den Bereich von 70 bis 79 fällt. Die Formel verwendet TRUE, damit der höchste Schwellenwert gefunden wird, der 76 nicht überschreitet:

Bei einem Nachschlagewert von 76 und TRUE liest VLOOKUP die sortierten Schwellenwerte der Reihe nach: 0, 60, 70, 90 ... Jeder Wert wird verglichen.

• 0 ist kleiner oder gleich 76: weitersuchen
• 60 ist kleiner oder gleich 76: weitersuchen
• 70 ist kleiner oder gleich 76: weitersuchen
• 90 ist größer als 76: anhalten

VLOOKUP geht zur letzten gültigen Zeile (70) zurück und gibt deren Stufe zurück: C. Eine ungefähre Übereinstimmung ist im Grunde eine Suche zwischen Schwellenwerten.

Wenn Sie das vierte Argument vollständig weglassen, verwendet VLOOKUP standardmäßig TRUE (ungefähre Übereinstimmung). Das überrascht viele, die ein exaktes Verhalten erwarten.

Eine Formel wie =VLOOKUP(A2, Data!A:B, 2) kann in einer unsortierten Liste unbemerkt einen falschen Wert zurückgeben. Die sichere Vorgehensweise: Geben Sie immer FALSE ein, außer Sie führen bewusst einen Stufenvergleich mit einer sortierten Tabelle durch.

Hier sehen Sie den Unterschied auf einen Blick:

• FALSE / exakt: Die Tabelle muss nicht sortiert sein, ein fehlender Wert gibt #N/A zurück, am besten für IDs und Codes geeignet
• TRUE / ungefähr: Die Tabelle muss aufsteigend sortiert sein, für Werte innerhalb des Bereichs wird nie #N/A zurückgegeben, am besten für Stufen und Bereiche geeignet

Treffen Sie Ihre Wahl anhand der Frage, die Sie beantworten möchten: „Ist genau dieser Eintrag vorhanden?“ verwendet FALSE; „In welche Stufe fällt dieser Wert?“ verwendet TRUE.

Für ein Gewicht in D2 sollen Versandkosten aus einer sortierten Stufentabelle in A2:B6 ermittelt werden (Schwellenwerte von 0, 1, 5, 10 und 20 kg). Die ungefähre Übereinstimmung wählt die richtige Stufe aus:

Wenn D2 den Wert 7 enthält, fällt das Gewicht in die 5-kg-Stufe, deren Kosten zurückgegeben werden. Ändern Sie das Gewicht, wird die Stufe sofort aktualisiert, ohne dass eine Liste mit jedem möglichen Gewicht erforderlich ist.

Auch die Leistung spielt eine subtile Rolle. Bei sehr großen, sortierten Tabellen kann eine ungefähre Übereinstimmung (TRUE) schneller sein, da die Tabellenkalkulation die sortierten Werte zügig überspringen kann, statt jede Zeile zu durchsuchen.

Geschwindigkeit geht jedoch niemals vor Korrektheit. Wenn Ihre Daten nicht sortiert sind oder Sie exakte IDs benötigen, wählen Sie immer FALSE. Eine schnell gelieferte falsche Antwort ist schlechter als eine etwas langsamere richtige. Bei modernen Tabellenkalkulationen und typischen Tabellengrößen ist der Unterschied selten spürbar. Verwenden Sie daher aus Sicherheitsgründen standardmäßig FALSE.

Wählen Sie für die jeweilige Situation den richtigen Übereinstimmungstyp.

Die wichtigsten Punkte:

• FALSE = exakte Übereinstimmung, eine unsortierte Tabelle ist in Ordnung, fehlende Werte geben #N/A zurück
• TRUE = ungefähre Übereinstimmung, die erste Spalte muss aufsteigend sortiert sein, der größte Wert wird gefunden, der Ihr Ziel nicht überschreitet
• Wenn Sie das Argument weglassen, wird standardmäßig TRUE verwendet. Geben Sie es daher immer an
• Verwenden Sie die exakte Übereinstimmung für IDs und Codes und die ungefähre für Stufen und Bereiche

Als Nächstes ändern Sie die Suchrichtung und durchsuchen mit HLOOKUP Zeilen.