Tabellen verbinden (VLOOKUP)

Was ist VLOOKUP

Die integrierte Funktion VLOOKUP ist eine der leistungsstärksten Funktionen in Excel. Es gehört zu den drei beliebtesten in Excel – nach SUM und AVERAGE. Ihre Aufgabe besteht darin, den gewünschten Wert in der Datentabelle zu finden und ihn in der angegebenen Zelle anzuzeigen.

Die Funktionssyntax umfasst einen Suchwert (was Sie suchen), eine Tabelle mit einer oder mehreren Spalten (wo gesucht werden soll), eine Spaltenindexnummer (aus welcher Spalte Daten zurückgegeben werden sollen) und einen Suchtyp (ein zusätzliches Argument). mit der Sie ein ungefähres oder genaues Ergebnis wählen können).

Die Funktion VLOOKUP wird häufig für Finanzanalysen, Datenverwaltung und die Arbeit mit Datenbanken verwendet. Mit dieser Funktion können Benutzer schnell und einfach Informationen in einem großen Datensatz finden, was Zeit sparen und die Genauigkeit verbessern kann.

Die Funktion VLOOKUP sucht Werte vertikal, also zwischen Zeilen (dies wird durch den ersten Buchstaben V – Vertical angezeigt). In Excel gibt es auch eine ähnliche Funktion HLOOKUP, die eine ähnliche Funktionalität hat, aber eine horizontale (H – Horizontal) Suche nach Spalten durchführt.

Ein Beispiel für ein Problem

Angenommen, wir haben zwei Tabellen mit Daten – eine Tabelle mit Krediten und eine Tabelle mit Sicherheiten:

Für die weitere Berichterstattung müssen wir Sicherheiten basierend auf der Vertragsnummer automatisch in die Kredittabelle einfügen.

Lösung

In Excel gibt es im Standardsatz von Funktionen in der Kategorie „Suchen und Referenzieren“ eine Funktion VLOOKUP. Diese Funktion sucht von oben nach unten in der Spalte ganz links der angegebenen Tabelle (Sicherheitentabelle) nach dem angegebenen Wert (in unserem Beispiel die Vertragsnummer) und zeigt beim Finden den Wert der angrenzenden Zelle (Art der Kreditsicherheit) an). Schematisch sieht die Funktionsweise der Funktion so aus:

Verwenden wir also die Funktion VLOOKUP. Wählen Sie die Zelle aus, in die sie eingegeben werden soll (E2) und öffnen Sie den Formelassistenten (Menü Insert - Function). Suchen Sie in der Kategorie (Suche und Referenz) nach der Funktion VLOOKUP und klicken Sie auf OK. Es erscheint ein Fenster zur Eingabe von Funktionsargumenten:

Füllen Sie sie einzeln aus:

• Nachschlagen Value– die Nummer der Vereinbarung, nach der die Funktion in der Spalte ganz links der Bereitstellungstabelle sucht. In unserem Fall die Vertragsnummer „KF568“ aus Zelle C2.
• Table Array- Die Tabelle, aus der wir die erforderlichen Werte erhalten, ist unsere Versorgungstabelle. Als Referenz verwenden wir den Bereich $G$2:$H$11 (der Bereich ist mit einem Dollarzeichen ($) fixiert, damit er beim Kopieren der Formel nicht nach unten wandert).
• Column Indexnummer- die laufende Nummer (kein Buchstabe!) der Spalte in der Bereitstellungstabelle, aus der wir den Namen der Bereitstellung entnehmen. Die erste Spalte der Bereitstellungstabelle hat die laufende Nummer 1, daher müssen wir die Art der Bereitstellung aus der zweiten Spalte (Nummer 2) entnehmen.
• Range Lookup- Wir können in dieses Feld nur zwei Werte eingeben: TRUE oder FALSE:
  
  1) Wenn wir den Wert 0 oder FALSE eingeben, bedeutet das eigentlich, dass die Suche nur erlaubt ist mit einer genauen Übereinstimmung, das heißt, wenn die Funktion die Vertragsnummer nicht in der Bereitstellungstabelle findet (sofern sie nicht vorhanden ist), wird der Fehler #N/A (keine Daten) angezeigt.
  
  2) Wenn wir einen Wert von 1 oder TRUE eingeben, bedeutet dies, dass nicht nur die genaue Suche erlaubt ist, sondern auch ungefährer Zufall. In den meisten Fällen kann diese ungefähre Ersetzung zu falschen Ergebnissen führen, wenn VLOOKUP die Werte anderer Transaktionen ersetzt. Daher sollten Sie die Näherungssuche nur für numerische Werte verwenden (aufsteigend vorsortiert).

Es bleibt nur noch OK zu drücken und die eingegebene Funktion über die gesamte Spalte zu kopieren.

Nachteile der Funktion VLOOKUP

Der Hauptnachteil besteht darin, dass die Suche nach dem gewünschten Wert nur in der ersten Spalte des angegebenen Bereichs erfolgen kann und die Funktion den gewünschten Wert nur aus den Spalten rechts davon zurückgeben kann.

Der zweite Nachteil von VLOOKUP besteht darin, dass die Funktion nicht mehr funktioniert, wenn Sie eine Spalte zur Nachschlagetabelle entfernen oder hinzufügen. Ein eingefügtes oder gelöschtes Element ändert das Ergebnis der Formel, da die Funktionssyntax erfordert, dass Sie den gesamten Bereich und die spezifische Spaltennummer angeben, aus der Sie Daten extrahieren möchten.

Außerdem hat die Funktion VLOOKUP eine Suchlängenbeschränkung von 255 Zeichen, andernfalls wird der Fehler #VALUE zurückgegeben!

Probleme beim Arbeiten mit der Funktion

Die Funktion VLOOKUP gibt einen Fehler (#N/A) zurück, wenn:

1. Exakte Suche ist erlaubt (Argument Range Lookup=0) und der gesuchte Wert ist nicht in der Tabelle (Table).
2. Ungefähre Suche erlaubt (Range Lookup=1), aber in der Tabelle (Table), in der wir die Werte nachschlagen, sind die Namen nicht in aufsteigender Reihenfolge sortiert.
3. Das Format der Zelle, aus der wir die Transaktionsnummer entnehmen (in unserem Fall zum Beispiel C2), und das Format der Zelle der ersten Spalte (G2:G11) der Tabelle sind unterschiedlich (zum Beispiel numerisch und Text). Diese Situation ist besonders typisch, wenn numerische Codes anstelle von Textnamen (Kontonummern, Identifikationscodes, Daten usw.) verwendet werden. In diesem Fall können Sie die Funktionen VALUE und TEXT zum Konvertieren von Datenformaten verwenden. Es sieht aus wie das:
   =VLOOKUP(TEXT(C2);$G$2:$H$11;0).
4. Die Funktion findet keinen Wert, da der Code Leerzeichen und unsichtbare nicht druckbare Zeichen (Bandvorschub usw.) enthält. In diesem Fall können Sie die Textfunktionen (TRIM) und (CLEAN) verwenden, um sie zu entfernen:
   =VLOOKUP(TRIM(CLEAN(C2));$G$2:$H$11;0).

Fehlerunterdrückung

Um die Fehlermeldung (#N/A) zu unterdrücken, wenn die Funktion keine genaue Übereinstimmung finden kann, können Sie die Funktion IFERROR verwenden. Diese Funktion prüft, ob der Fehler (#N/A) das Ergebnis von VLOOKUP ist, und wenn ja, gibt sie eine leere Zeichenfolge ("") oder null zurück, andernfalls das Ergebnis von VLOOKUP.

Artikel zum Thema:

• Verbesserte Funktion VLOOKUP (VLOOKUP2)
• VLOOKUP zur Kombination nach zwei Bedingungen (VLOOKUP3)
• Schnelle Zusammenführung großer Tabellen (VLOOKUP2D)