Vergleich von SVERWEIS und XVERWEIS: Die richtige Suchformel auswählen

Verwenden Sie XVERWEIS für die meisten neuen Suchvorgänge, wenn Ihre Arbeitsmappe in LibreOffice 24.8 oder höher geöffnet wird. Standardmäßig wird eine exakte Übereinstimmung gesucht, und Sie können separate Such- und Ergebnisbereiche festlegen. Verwenden Sie SVERWEIS, wenn Sie Kompatibilität mit älteren Calc-Versionen benötigen oder wenn sich der Suchwert in der ersten Spalte einer kompakten Tabelle befindet. Wenn Sie SVERWEIS beibehalten, geben Sie als viertes Argument 0 oder FALSCH an, um eine exakte Übereinstimmung zu erzielen.

Diese Funktionen lösen im Grunde dasselbe Problem: Sie finden einen Schlüssel, beispielsweise einen Produktcode, und geben zugehörige Informationen aus einer anderen Zelle zurück. Die wesentlichen Unterschiede liegen darin, wie die Formeln ihre Bereiche definieren, welche Annahmen getroffen werden, wenn eine Option weggelassen wird, und ob die Datei auch mit älterer Software kompatibel sein muss.

Worin besteht der praktische Unterschied?

Die Funktion SVERWEIS durchsucht die erste Spalte einer rechteckigen Tabelle und gibt einen Wert aus der nummerierten Spalte rechts daneben zurück. XVERWEIS trennt den Suchbereich vom Ergebnisbereich, sodass die Ergebniswerte auf beiden Seiten der Schlüssel stehen können. XVERWEIS akzeptiert außerdem einen benutzerdefinierten Wert für eine fehlende Übereinstimmung und kann in umgekehrter Reihenfolge suchen.

Angenommen, die Zellen A2:A5 enthalten Produkt-IDs und die Zellen B2:B5 Preise. Zelle F2 enthält die zu suchende ID. In Calc lautet die Formel für die exakte Übereinstimmung mit SVERWEIS:

=VLOOKUP(F2;A2:B5;2;0)

Die entsprechende XLOOKUP-Formel lautet:

=XLOOKUP(F2;A2:A5;B2:B5;"Not found")

Beide Funktionen liefern den entsprechenden Preis. Calc zeigt üblicherweise Semikolons zwischen Funktionsargumenten gemäß den Gebietsschemaeinstellungen an; verwenden Formeln in Ihrer Installation Kommas, nutzen Sie das von Calc erwartete Trennzeichen. Die Beispiele verwenden Ergebnisse aus einer einzelnen Zelle und eindimensionale Suchbereiche.

Wann sollten Sie XLOOKUP wählen?

Wählen Sie XVERWEIS für eine neue Arbeitsmappe, wenn Sie wissen, dass die Empfänger LibreOffice 24.8 oder neuer verwenden. Laut der offiziellen XVERWEIS-Hilfeseite ist diese Funktion ab LibreOffice 24.8 verfügbar . Der Standard-Suchmodus ist „Exakt“, sodass bei einem fehlenden Suchschlüssel nicht automatisch ein ähnlicher Wert zurückgegeben wird.

Das Funktionsmuster ist wie folgt =XLOOKUP(search_criterion;search_array;result_array;[if_not_found];[match_mode];[search_mode]): Die ersten drei Argumente geben den Schlüssel, den eindimensionalen Suchbereich und den Rückgabebereich an. Ein viertes Argument, beispielsweise `NV`, "Not found"ersetzt das übliche Ergebnis `#NV`, wenn keine Übereinstimmung gefunden wird.

Gibt einen Wert aus der Spalte links zurück.

Die Funktion SVERWEIS kann keine Werte links von ihrer ersten Tabellenspalte zurückgeben. XVERWEIS hingegen schon, da Such- und Ergebnisbereich unabhängig sind. Wenn sich beispielsweise die IDs in C2:C100 und die Abteilungsnamen in A2:A100 befinden, verwenden Sie:

=XLOOKUP(F2;C2:C100;A2:A100;"ID not found")

Dieses Layout vermeidet zudem einen anfälligen Spaltenindex. Wenn jemand eine Spalte in eine SVERWEIS-Tabelle einfügt, kann der fest codierte Index auf ein anderes Feld verweisen. Der explizite Ergebnisbereich von XVERWEIS erleichtert die Überprüfung der gewünschten Ergebnisspalte.

Den letzten übereinstimmenden Eintrag zurückgeben

Wenn doppelte Schlüssel vorhanden sind und Sie die letzte Übereinstimmung suchen, unterstützt XVERWEIS die umgekehrte Suche in seinem Suchmodus. Bei einem vertikalen Bereich -1durchsucht das letzte Argument vom letzten zum ersten Treffer:

=XLOOKUP(F2;A2:A100;B2:B100;"Not found";0;-1)

Diese Funktion gibt das Ergebnis des letzten exakten Vorkommens zurück. Verwenden Sie sie nur, wenn die Zeilenreihenfolge eine bestimmte Bedeutung hat, z. B. wenn die neuesten Datensätze zuletzt erscheinen. Sollen Duplikate als Fehler behandelt werden, bereinigen oder validieren Sie die Daten, anstatt implizit ein Vorkommen auszuwählen.

Wann ist SVERWEIS noch die bessere Wahl?

Verwenden Sie SVERWEIS, wenn die Datei in einer älteren LibreOffice-Version geöffnet werden muss, die XVERWEIS nicht unterstützt, oder wenn die Zielanwendung XVERWEIS möglicherweise nicht erkennt. Die offizielle Calc-Dokumentation beschreibt die Tabellen- und Indexargumente sowie das optionale Argument für den sortierten Bereich von SVERWEIS; konsultieren Sie die offizielle Referenz der Tabellenkalkulationsfunktionen .

Für eine exakte Übereinstimmung lautet das Muster =VLOOKUP(key;table;column_index;0). Der Index zählt die Spalten vom linken Rand der ausgewählten Tabelle: 1 steht für die erste Spalte, 2 für die zweite. Der Suchschlüssel muss in dieser ersten Spalte stehen, und das zurückgegebene Feld muss sich rechts daneben befinden.

Das vierte Argument ist wichtig. Wenn Sie es weglassen oder auf WAHR setzen, verwendet SVERWEIS eine ungefähre Übereinstimmung und erwartet, dass die erste Spalte aufsteigend sortiert ist. Dieses Verhalten kann für Bereiche wie Steuerklassen nützlich sein, bei denen jede Zeile eine Untergrenze markiert. Es ist jedoch riskant für IDs, Namen und unsortierte Listen. Geben Sie in diesen Fällen 0 oder FALSCH an.

Wie verhalten sich Übereinstimmung und Fehlerverhalten im Vergleich?

• Exakte Übereinstimmung: XVERWEIS verwendet standardmäßig eine exakte Übereinstimmung. SVERWEIS erfordert, dass das vierte Argument auf 0 oder FALSCH gesetzt wird, um eine garantierte exakte Suche zu gewährleisten.
• Fehlender Schlüssel: XVERWEIS kann im vierten Argument eine ausgewählte Meldung oder einen Wert zurückgeben. SVERWEIS gibt in der Regel #NV zurück; verwenden Sie WENNNV, wenn Sie eine benutzerdefinierte Meldung benötigen.
• Ungefähre Übereinstimmung: Beide unterstützen bereichsbasierte Übereinstimmungen, jedoch müssen die Daten und der gewählte Modus der vorgesehenen Regel entsprechen. Sortieren Sie die Daten nach Bedarf und überprüfen Sie die Grenzfälle.
• Suchrichtung: XVERWEIS kann mit dem Argument Suchmodus von unten nach oben suchen. SVERWEIS gibt bei normaler Verwendung von oben nach unten die erste übereinstimmende Zeile zurück.

Um bei fehlendem Schlüssel mit SVERWEIS eine benutzerdefinierte Fehlerbehandlung zu implementieren, verwenden Sie die entsprechende Option =IFNA(VLOOKUP(F2;A2:B5;2;0);"Not found"). Dadurch bleibt die Einstellung für die exakte Übereinstimmung erhalten und nur der Fehler „Nicht verfügbar“ wird behandelt. Vermeiden Sie es, die Formel in eine allgemeine Fehlerbehandlung einzuschließen, es sei denn, Sie möchten auch andere Probleme, wie z. B. einen ungültigen Bereichsbezug, ausblenden.

Was sollten Sie vor dem Teilen einer Arbeitsmappe überprüfen?

Die Hilfe von LibreOffice gibt an, dass die Funktion XVERWEIS ab Version 24.8 verfügbar ist. Sie weist außerdem darauf hin, dass XVERWEIS nicht Teil des OpenFormula-Standards von OpenDocument Version 1.3 ist und verweist für die Interoperabilität auf einen Microsoft-Funktionsnamensraum. Das bedeutet, dass das Dateiformat allein nicht garantiert, dass jede ältere oder Drittanbieter-Tabellenkalkulationsanwendung die Funktion korrekt berechnet. In heterogenen Softwareumgebungen sollten Sie die Arbeitsmappe in der ältesten Zielanwendung testen oder eine etablierte Formel wie z. B. SVERWEIS (exakte Übereinstimmung) verwenden, sofern deren Format kompatibel ist.

Beachten Sie außerdem die dokumentierte Bedingung für Arrayformeln: Wenn das Ergebnis von XVERWEIS ein Zellbereich ist, muss die Funktion als Arrayformel eingegeben werden. Die obigen Beispiele geben jeweils einen einzelnen Wert zurück. Soll eine Formel mehrere Zellen zurückgeben, konsultieren Sie die versionsspezifische Hilfe und testen Sie, wie die Zielanwendung das Array-Ergebnis verarbeitet.

Wie lässt sich das Ergebnis überprüfen?

1. Prüfen Sie einen vorhandenen Schlüssel und vergleichen Sie den zurückgegebenen Wert mit der zugehörigen Quellzeile.
2. Testen Sie einen nicht vorhandenen Schlüssel. Vergewissern Sie sich, dass XVERWEIS den von Ihnen gewählten Ausweichwert anzeigt oder dass SVERWEIS die erwartete Meldung #NV oder IFNA zurückgibt.
3. Bei der Verwendung von SVERWEIS für exakte Übereinstimmungen prüfen Sie, ob das letzte Argument 0 oder FALSCH ist. Wenn Sie absichtlich eine ungefähre Übereinstimmung verwenden, prüfen Sie die Sortierreihenfolge sowie die Werte direkt oberhalb, auf und unterhalb einer Grenze.
4. Bei XVERWEIS mit einer Rückgabespalte links oder einer umgekehrten Suche wird ein Schlüssel mit einem bekannten Ergebnis getestet und, falls Duplikate vorhanden sind, bestätigt, welches Vorkommen zurückgegeben werden soll.
5. Speichern Sie die Arbeitsmappe und öffnen Sie sie erneut in der ältesten Calc- oder Tabellenkalkulationsanwendung, die Ihre Empfänger verwenden. Ein korrektes Ergebnis auf Ihrem Computer beweist nicht, dass eine ältere Version dieselbe Funktion unterstützt.

Für die meisten neuen Calc-Tabellen bietet XLOOKUP übersichtlichere Bereiche und sicherere Standardeinstellungen für die exakte Übereinstimmung. Verwenden Sie SVERWEIS, wenn Abwärtskompatibilität wichtig ist oder die Anordnung der Ergebnisse in der ersten Spalte rechts bereits geeignet ist, und legen Sie den Suchmodus explizit fest.