LibreOffice Calc VLOOKUP vs. XLOOKUP: Einsteigerfreundlicher Funktionsleitfaden

LibreOffice Calc bietet Ihnen mehrere Möglichkeiten, Werte in einer Tabelle zu finden und zugehörige Daten abzurufen. Jahrelang war SVERWEIS die gängige Wahl. Heute enthält Calc auch XVERWEIS , eine neuere Suchfunktion, die einige Einschränkungen von SVERWEIS beseitigt und in der Regel einfacher zu warten ist.

Diese Anleitung beginnt mit dem grundlegenden Denkmodell und führt dann durch praktische Formeln, die Sie in eine kleine Tabellenkalkulation kopieren können. Außerdem wird erklärt, wo INDEX/VERGLEICH noch sinnvoll ist, was schiefgehen kann und welche Funktion für eine neue Arbeitsmappe geeignet ist.

Was sollten Sie vor der Verwendung von Lookup-Funktionen wissen?

Eine Nachschlagefunktion durchsucht einen Bereich nach einem Schlüssel, beispielsweise einer Produkt-ID, und gibt dann einen zugehörigen Wert zurück, beispielsweise den Produktpreis. Der Suchschlüssel kann Text, eine Zahl, ein Datum oder eine Zellreferenz sein.

In der aktuellen LibreOffice-Hilfe wird XLOOKUP als moderner Ersatz für VLOOKUP, HLOOKUP und LOOKUP beschrieben. XLOOKUP ist seit LibreOffice 24.8 verfügbar. Die aktuelle LibreOffice-Hilfe zu Version 26.8 dokumentiert die exakte und ungefähre Suche, die Suche mit Platzhaltern und regulären Ausdrücken, die umgekehrte Suche und die Binärsuche. Siehe die offizielle LibreOffice-Dokumentation zu XLOOKUP .

Ein Detail, das Nutzer von Microsoft Excel oft überrascht: LibreOffice-Formelbeispiele verwenden häufig Semikolons zwischen den Argumenten. Zum Beispiel: =XLOOKUP(F2;A2:A6;D2:D6). Calc passt die Trennzeichen möglicherweise an Ihre Ländereinstellung an. Verwenden Sie daher das von Ihrer Installation akzeptierte Trennzeichen.

VLOOKUP vs. XLOOKUP auf einen Blick

Bereiten Sie einen kleinen Übungstisch vor.

Erstellen Sie die Spalten A bis D mit den Überschriften Produkt-ID , Produktname , Kategorie und Preis . Fügen Sie einige IDs wie P001 bis P005 hinzu. Tragen Sie die zu suchende ID in Zelle F2 und Ihre Formel in Zelle G2 ein.

Formeln in Calc beginnen mit einem Gleichheitszeichen. Sie können sie direkt in eine Zelle oder in die Bearbeitungsleiste eingeben; die offizielle LibreOffice-Formelanleitung beschreibt beide Methoden.

Schritt 1: Verwenden Sie SVERWEIS für eine einfache exakte Übereinstimmung

Angenommen, P004 befindet sich in Spalte F2 und Sie möchten den Preis aus Spalte D. Geben Sie Folgendes ein:

=VLOOKUP(F2;$A$2:$D$6;4;0)

Die Argumente bedeuten: Suche den Wert in Zelle F2, durchsuche die erste Spalte von A2:D6, gib den Wert aus der vierten Spalte dieser Tabelle zurück und erfordere eine exakte Übereinstimmung. Die Dollarzeichen erzeugen absolute Bezüge , wodurch der Suchbereich beim Kopieren der Formel in andere Zellen beibehalten wird.

LibreOffice beschreibt die Funktion SVERWEIS als vertikale Suche, bei der der Suchwert in der ersten Spalte der Tabelle stehen muss und der Rückgabewert aus der Spalte rechts daneben stammt. Es wird außerdem darauf hingewiesen, dass die Funktion sortierte Daten voraussetzt und nur eine ungefähre Übereinstimmung liefert, wenn das letzte Argument weggelassen oder auf WAHR gesetzt wird. Konsultieren Sie die offizielle LibreOffice-Referenz für Tabellenkalkulationsfunktionen, bevor Sie sich auf das ungefähre Verhalten von SVERWEIS verlassen.

Schritt 2: Verwenden Sie XVERWEIS, wenn sich die Spalte mit den Rückgabewerten links befindet.

Ordnen Sie die Tabelle nun so an, dass der Produktname in Spalte A und die Produkt-ID in Spalte C steht. Bei Verwendung von SVERWEIS ist diese Anordnung unpraktisch, da die Funktion nur die erste Spalte ihres Tabellenarrays durchsucht und die Werte rechts davon zurückgibt.

XVERWEIS trennt das Sucharray vom Ergebnisarray . Um P003 in Spalte C zu finden und den Produktnamen aus Spalte A zurückzugeben, verwenden Sie:

=XLOOKUP(F2;$C$2:$C$6;$A$2:$A$6;"Not found")

Das vierte Argument ist der Wert, der zurückgegeben wird, wenn keine Übereinstimmung gefunden wird. Dadurch ist die Formel lesbarer, als wenn man für diesen häufigen Fall eine separate Fehlerbehandlungsfunktion für die Suche verwenden würde.

Schritt 3: Fehlende IDs behandeln, ohne #N/A anzuzeigen

Gibt ein Benutzer eine nicht existierende ID ein, liefert eine einfache exakte Suche normalerweise ein Ergebnis von 0. #N/AIn XLOOKUP wird im vierten Argument ein benutzerfreundliches Ergebnis angegeben.

=XLOOKUP(F2;$A$2:$A$6;$D$2:$D$6;"Not found";0)

Der 0Suchmodus fordert explizit eine exakte Übereinstimmung an, obwohl dies bereits die Standardeinstellung von XVERWEIS ist. Die Angabe des Suchmodus kann dennoch sinnvoll sein, wenn die Absicht der Formel für jemanden, der das Tabellenblatt überprüft, sofort ersichtlich sein soll.

Nicht jeder Fehler sollte automatisch ausgeblendet werden. Eine ID, die immer vorhanden sein sollte, #N/Akann wertvoll sein, da sie fehlerhafte oder unvollständige Quelldaten aufdeckt. Verwenden Sie eine benutzerfreundliche Ausweichlösung, wenn fehlende Schlüssel erwartet werden, nicht aber, wenn sie auf ein Datenqualitätsproblem hinweisen.

Schritt 4: Suchen Sie von unten nach dem neuesten übereinstimmenden Datensatz.

Manchmal kommt ein Schlüssel mehrfach vor. Beispielsweise kann ein Transaktionsprotokoll mehrere Zeilen für den Kunden C001 enthalten. Die Funktion XVERWEIS verfügt über das Argument Suchmodus . Der Wert „ -1Suchen“ richtet die Suche vom letzten Eintrag zum ersten.

Um den Betrag aus der letzten Zeile mit der Kunden-ID in Feld F2 abzurufen, verwenden Sie:

=XLOOKUP(F2;$A$2:$A$8;$D$2:$D$8;"Not found";0;-1)

Die offizielle XLOOKUP-Referenz definiert den Suchmodus 1als „vom ersten zum letzten“ und „ -1vom letzten zum ersten“. Sie bietet außerdem die Modi „binäre Suche“ 2und „binäre Suche -2“, diese erfordern jedoch korrekt sortierte Daten. Die binäre Suche in unsortierten Daten kann zu ungültigen Ergebnissen führen.

Wann sollte man INDEX und MATCH noch verwenden?

Bevor XVERWEIS in Calc 24.8 eingeführt wurde, war eine gängige, flexible Alternative die Kombination aus INDEX und VERGLEICH . VERGLEICH ermittelt die relative Position eines Schlüssels; INDEX gibt den Wert an dieser Position zurück. Zum Beispiel:

=INDEX($D$2:$D$6;MATCH(F2;$A$2:$A$6;0))

Dies ist weiterhin nützlich, wenn Kompatibilität mit älteren LibreOffice-Installationen erforderlich ist oder wenn eine bestehende Arbeitsmappe bereits INDEX/VERGLEICH durchgängig verwendet. Die offizielle Referenz für Tabellenkalkulationsfunktionen dokumentiert sowohl INDEX als auch VERGLEICH .

Calc bietet ab LibreOffice 24.8 auch XMATCH. XMATCH erweitert die Such- und Übereinstimmungsfunktionen von XVERWEIS um Funktionen, einschließlich der umgekehrten Suche. Weitere Informationen finden Sie in der offiziellen LibreOffice XMATCH-Dokumentation .

Häufige Fehler, die es zu vermeiden gilt

Wenn Sie eine exakte Übereinstimmung benötigen, lassen Sie das letzte Argument von SVERWEIS leer.

Bei der Funktion SVERWEIS wird durch Weglassen des letzten Arguments das Verhalten für sortierte Bereiche aktiviert. Verwenden Sie für Produkt-IDs, Mitarbeiternummern, Artikelnummern und andere eindeutige Kennungen 0FALSCH für eine exakte Übereinstimmung, es sei denn, eine ungefähre Übereinstimmung ist beabsichtigt.

Falscher Spaltenindex in der VLOOKUP-Funktion

Der Spaltenindex der SVERWEIS-Funktion wird ab der ersten Spalte der Tabellenmatrix gezählt, nicht ab den Spaltenbuchstaben der Tabelle. In A2:D6 hat Spalte D den Index 4. Wenn Sie später eine Spalte in diese Tabelle einfügen, verweist der fest codierte Index möglicherweise nicht mehr auf das gewünschte Feld.

Verwendung von Bereichen mit unterschiedlichen Längen in XLOOKUP

Such- und Ergebnisarray sollten zeilen- bzw. spaltenweise ausgerichtet sein. Ein Suchbereich wie A2:A100 in Kombination mit D2:D90 ist ein Designfehler, da die entsprechenden Positionen nicht dieselben Datensätze umfassen.

Anwendung der Binärsuche auf unsortierte Daten

Der Suchmodus XLOOKUP 2 erwartet eine aufsteigende, der Modus -2 eine absteigende Sortierung. Die LibreOffice-Dokumentation weist ausdrücklich darauf hin, dass unsortierte Daten zu ungültigen Ergebnissen führen können. Wenn Sie die Sortierung nicht steuern, verwenden Sie die normale Vorwärts- oder Rückwärtssuche.

Verwechslung von Wildcards mit regulären Ausdrücken

Calc unterstützt Platzhalter- und reguläre Ausdruckssuche, diese sind jedoch in XLOOKUP separate Suchmodi. Im Platzhaltermodus werden Zeichen wie „ *und“ und „ “ verwendet ?; im Modus für reguläre Ausdrücke kommt die reguläre Ausdrucks-Engine von Calc zum Einsatz. Wählen Sie den Modus bewusst aus, anstatt davon auszugehen, dass ein Suchmuster automatisch interpretiert wird.

Welche Lookup-Funktion sollte ein Anfänger wählen?

Für neue Tabellenkalkulationen unter LibreOffice 24.8 oder höher empfiehlt sich die Verwendung von XVERWEIS. Die separaten Such- und Ergebnisbereiche sind übersichtlicher, die exakte Übereinstimmung ist standardmäßig aktiviert, Zeilenumbrüche auf der linken Seite sind unkompliziert, und die integrierten Optionen für „Nicht gefunden“ und die umgekehrte Suche reduzieren den Bedarf an umständlichen Formeln.

Verwenden Sie SVERWEIS, wenn Sie eine bestehende, darauf basierende Arbeitsmappe pflegen, sie in Umgebungen verwenden, in denen die Unterstützung von XVERWEIS unsicher ist, oder mit einer einfachen, stabilen Tabelle arbeiten, bei der die Einschränkungen keine Rolle spielen. Verwenden Sie INDEX/VERGLEICH, wenn Kompatibilität mit älteren LibreOffice-Versionen wichtig ist oder die Arbeitsmappe bereits diesem Muster entspricht.

Die wichtigste Gewohnheit ist nicht, sich jedes Argument zu merken. Stattdessen sollten Sie vor dem Aufschreiben der Formel vier Dinge klären: Welchen Wert suche ich? Wo suche ich? Welcher Bereich soll das Ergebnis liefern? Und was soll passieren, wenn der Schlüssel fehlt? Sobald diese Punkte geklärt sind, wird die Wahl zwischen SVERWEIS, XVERWEIS und INDEX/VERGLEICH deutlich einfacher.