So verwenden Sie exakte und ungefähre Übereinstimmungen mit VLOOKUP in Excel
VLOOKUP ist eine häufig verwendete Funktion in Excel, um gezielt Informationen in großen Datensätzen zu finden. Indem sie auf einen Wert in der äußerst linken Spalte Ihrer Tabelle verweist, ruft VLOOKUP zugehörige Daten aus anderen Spalten derselben Zeile ab. Trotz ihrer Beliebtheit stoßen einige Anwender auf Herausforderungen – sei es durch falsche Parametereinstellungen, unzureichende Fehlerbehandlung oder spezielle Suchanforderungen. Dieser umfassende Leitfaden erklärt sowohl die Verwendung exakter als auch ungefährer Übereinstimmungen mit VLOOKUP, zeigt, wann jede Methode am besten geeignet ist, beleuchtet integrierte und alternative Lösungen und liefert praktische Tipps zur Fehlersuche für effizienteres Arbeiten.
Verwenden der VLOOKUP-Funktion, um exakte Übereinstimmungen in Excel zu erhalten
VLOOKUP zur Ermittlung exakter Übereinstimmungen mit einer praktischen Funktion
Verwenden der VLOOKUP-Funktion, um ungefähre Übereinstimmungen in Excel zu erhalten
Verwendung der INDEX- und VERGLEICH-Funktionen für flexible Suchvorgänge (Alternative zu VLOOKUP)
VBA-Code zur Automatisierung der Suche nach exakten und ungefähren Übereinstimmungen
Verwenden der VLOOKUP-Funktion, um exakte Übereinstimmungen in Excel zu erhalten
Bevor Sie VLOOKUP verwenden, sollten Sie die Syntax und die Funktion jedes Parameters im Kontext Ihrer Daten verstehen.
Hier ist die Standard-VLOOKUP-Funktion in Excel:
- Suchkriterium: Der Wert, nach dem Sie in der ersten Spalte Ihrer ausgewählten Tabelle suchen möchten.
- Tabellenmatrix: Der Zellbereich, der Ihre Daten enthält (z. B. A1:D10), oder ein benannter Bereich.
- Spaltenindexnummer: Die Nummer der Spalte innerhalb des Datenbereichs, aus der das Ergebnis abgerufen werden soll.
- Bereich_Verweis: Optionaler Parameter. Verwenden Sie FALSCH für eine exakte Übereinstimmung oder WAHR für eine ungefähre Übereinstimmung (oder lassen Sie ihn weg – der Standardwert ist WAHR).
Angenommen, Sie haben eine Liste mit Personeninformationen im Zellbereich A2:D12, wie unten dargestellt:
Wenn Sie die Namen zu den in Spalte F aufgeführten IDs abrufen möchten, geben Sie die folgende Formel in eine leere Zelle ein – beispielsweise G2 –, in der das Ergebnis angezeigt werden soll:
Drücken Sie „Enter“ und ziehen Sie anschließend am Ausfüllkästchen nach unten, um die Formel auf weitere Zeilen zu übertragen – so erhält jede relevante ID automatisch den zugehörigen Namen. Das Ergebnis sieht dann wie folgt aus:
Erläuterung und Tipps:
1. F2: Zelle mit dem Suchwert (zu suchende ID).
2. A2:D12: Der Datenbereich, der die Tabelle einschließlich IDs und Namen umfasst.
3. 2: Spaltenindexnummer, die auf die zweite Spalte (Namen) innerhalb Ihres ausgewählten Bereichs verweist.
4. FALSCH: Stellt sicher, dass die Funktion ausschließlich exakte Übereinstimmungen für die ID findet.
5. Wenn der exakte Wert im Bereich fehlt, zeigt Excel den #NV-Fehler an – das bedeutet, dass keine Übereinstimmung gefunden wurde. Prüfen Sie daher die Datenqualität und Rechtschreibung.
6. Vermeiden Sie unbeabsichtigte Bezugsfehler – achten Sie darauf, dass Ihre Bereiche beim Kopieren der Formel fixiert sind (mithilfe von $-Zeichen).
7. Wenn sich Ihre Suchtabelle möglicherweise in der Größe ändert, sollten Sie benannte Bereiche verwenden, um die Stabilität Ihrer Formeln zu verbessern.
VLOOKUP zur Ermittlung exakter Übereinstimmungen mit einer praktischen Funktion
Anwender, die schnellere und interaktivere Suchfunktionen in Excel wünschen, können Kutools für Excel nutzen. Die Funktion Daten in einem Bereich suchen vereinfacht Suchvorgänge – besonders für Benutzer, die keine Formeln eingeben möchten oder zusätzliche Unterstützung brauchen.
Sobald Kutools für Excel installiert ist, führen Sie diese praktischen Schritte aus:
1. Wählen Sie die Zelle aus, in der das Suchergebnis angezeigt werden soll.
2. Navigieren Sie über Kutools > Formelhelfer > Formelhelfer, wie hier gezeigt:
3. Im Dialogfeld Formelhelfer:
- Wählen Sie unter Formeltyp die Kategorie Suche.
- Wählen Sie in der Formelliste Daten in einem Bereich suchen.
- Füllen Sie die Argumenteingabefelder aus:
- Klicken Sie auf das erste
, um Ihre Tabellenmatrix auszuwählen. - Klicken Sie auf das zweite für den Suchwert (z. B. die Zelle mit ID oder Name).
- Klicken Sie auf das dritte , um die Spalte auszuwählen, aus der Sie Daten extrahieren möchten.
, um Ihre Tabellenmatrix auszuwählen.
4. Klicken Sie auf OK. Der erste gefundene Wert wird sofort angezeigt. Verwenden Sie das Ausfüllkästchen, um die Formel bei Bedarf nach unten zu kopieren.
Tipps zur Verwendung:
- Diese Methode ist ideal für Benutzer, die Suchvorgänge lieber per Klick konfigurieren, als manuell Formeln zu erstellen.
- Sollte das Element nicht gefunden werden, gibt Kutools #NV zurück – genau wie die Standard-VLOOKUP-Funktion. Prüfen Sie Ihre Eingabewerte und das Datenformat.
- Achten Sie darauf, Kutools regelmäßig zu aktualisieren, um stets Zugriff auf neue Funktionen und Verbesserungen zu haben.
Laden Sie Kutools für Excel jetzt herunter und testen Sie es kostenlos!
Verwenden der VLOOKUP-Funktion, um ungefähre Übereinstimmungen in Excel zu erhalten
Wenn der gesuchte Wert in Ihrer Liste nicht vorhanden ist, müssen Sie stattdessen den nächstgelegenen oder nächsthöheren Wert ermitteln. Dieser Fall tritt häufig bei Preislisten, Notengrenzen oder Provisionstabellen auf. VLOOKUP unterstützt ungefähre Übereinstimmungen über den Parameter WAHR im letzten Argument.
Angenommen, Sie haben diese Daten, wobei die gewünschte Menge (wie 58) nicht direkt in der Mengenspalte enthalten ist, Sie aber dennoch den nächstgelegenen zugehörigen Stückpreis ermitteln müssen:
Geben Sie diese Formel in eine leere Zelle ein, z. B. C2:
Drücken Sie die Enter-Taste und ziehen Sie das Ausfüllkästchen nach unten, um weitere Zeilen automatisch auszufüllen. Excel liefert dabei ungefähre Übereinstimmungen basierend auf Ihrem angegebenen Suchwertbereich – wie hier dargestellt:
Wichtige Hinweise:
1. D2 = Suchwert (die Menge, die Sie abgleichen möchten).
2. A2:B10 = Tabellenbereich mit Mengen und Preisen.
3. 2 = Zweite Spalte (Stückpreis) für den Rückgabewert.
4. WAHR = Aktiviert den ungefähren Abgleich. VLOOKUP sucht den größten Wert, der kleiner oder gleich Ihrem Suchwert ist.
5. Sortierung ist unerlässlich: Stellen Sie sicher, dass die erste Spalte (Menge) in aufsteigender Reihenfolge sortiert ist – andernfalls können die Ergebnisse unvorhersehbar oder falsch sein.
6. Bei komplexen Schwellenwerten wie Provisionssätzen hilft dieser Ansatz, passende Sätze basierend auf Bereichen schnell zu identifizieren.
7. Wenn Sie den ungefähren Abgleich für granulare Werte wie Noten, Bereiche oder gleitende Skalen verwenden, prüfen Sie stets vor Anwendung oder Weitergabe von Formeln die Tabellenstruktur und Sortierung.
Verwendung der INDEX- und VERGLEICH-Funktionen für flexible Suchvorgänge (Alternative zu VLOOKUP)
In vielen Fällen ist VLOOKUP ungeeignet – besonders, wenn sich Ihre Suchdaten nicht in der ersten Spalte befinden oder Sie horizontal bzw. flexibel innerhalb von Datensätzen suchen möchten. Die Kombination aus INDEX und VERGLEICH bietet hier eine leistungsstarke Alternative für sowohl exakte als auch ungefähre Abgleiche: Sie ist unabhängig von der Spaltenreihenfolge und ermöglicht Suchvorgänge in jede Richtung.
Diese Lösung kommt häufig in Szenarien wie der Suche nach Mitarbeiterdaten zum Einsatz, wenn sich ID oder Name möglicherweise nicht in der äußersten linken Spalte befinden, oder zum Vergleich von Werten in nicht benachbarten Bereichen.
Vorteile: Funktioniert sowohl für vertikale als auch horizontale Abfragen, erfordert keine Sortierung und ermöglicht komplexe Abgleichsbedingungen.
Nachteile: Etwas komplexer einzurichten als VLOOKUP und erfordert ein Verständnis für verschachtelte Funktionen.
1. Um einen exakten Abgleich durchzuführen – beispielsweise die Abteilung eines Mitarbeiters anhand seiner ID zu ermitteln – geben Sie die folgende Formel in eine leere Zelle wie G2 ein:
=INDEX($C$2:$C$12,MATCH(F2,$A$2:$A$12,0))Dabei ist F2 Ihr Suchwert (die Mitarbeiter-ID), $A$2:$A$12 der Bereich, in dem die ID gesucht wird, und $C$2:$C$12 die Spalte mit den Abteilungsnamen. Die 0 in VERGLEICH steht für „exakten Abgleich“.
Drücken Sie die Eingabetaste und ziehen Sie die Formel nach unten, um sie auf weitere Zeilen anzuwenden. Falls die ID nicht gefunden wird, erhalten Sie einen #NV-Fehler. Für eine noch reibungslosere Benutzererfahrung empfehlen wir die Verwendung von WENNFEHLER oder einer Datenüberprüfung.
2. Verwenden Sie für ungefähre Abgleiche die folgende Formel (z. B. zum Finden einer Notengrenze):
=INDEX($B$2:$B$10,MATCH(D2,$A$2:$A$10,1))Dabei ist D2 der Suchwert, $A$2:$A$10 der sortierte Referenzbereich (in aufsteigender Reihenfolge) und $B$2:$B$10 enthält die Rückgabewerte. Die 1 in VERGLEICH aktiviert den ungefähren Abgleich und gibt den größten Wert zurück, der kleiner oder gleich Ihrem Suchwert ist.
Beachten Sie: Wenn Sie Formeln kopieren, verwenden Sie absolute Bezüge für Tabellenbereiche, um korrekte Abfragen zu gewährleisten. Setzen Sie WENNFEHLER ein, um anstelle von Fehlermeldungen benutzerfreundliche leere Felder oder individuelle Hinweise anzuzeigen.
Fehlerbehebungstipps: Sollte Ihre Formel einen Fehler zurückgeben, überprüfen Sie die Sortierung bei ungefähren Abgleichen, kontrollieren Sie die Zellbereiche und stellen Sie sicher, dass der Suchwertbereich korrekt formatiert ist (z. B. Text im Vergleich zu Zahlen).
VBA-Code zur Automatisierung der Suche nach exakten und ungefähren Übereinstimmungen
Für fortgeschrittene Anwender oder komplexe, sich wiederholende Abfrageaufgaben vereinfacht ein VBA-Makro die Suche nach exakten oder ungefähren Übereinstimmungen – besonders dann, wenn mehrere Abfragen durchgeführt, Ergebnisse in neue Arbeitsblätter exportiert oder Datenverarbeitungen automatisiert werden sollen, die mit Standard-Excel-Formeln nicht möglich sind.
Diese Methode eignet sich ideal, wenn sich Ihr Suchbereich oder Ihre Kriterien häufig ändern oder die Suche in umfangreichere automatisierte Arbeitsabläufe integriert werden soll.
1. Öffnen Sie zunächst den VBA-Editor: Gehen Sie zu Entwicklertools > Visual Basic. Klicken Sie im erscheinenden Fenster auf Einfügen > Modul.
Fügen Sie den folgenden VBA-Code in das Modul ein:
Sub KutoolsVLookupMacro()
Dim lookupValue As Variant
Dim lookupRange As Range
Dim colNum As Integer
Dim rangeType As String
Dim result As Variant
Dim xTitleId As String
xTitleId = "KutoolsforExcel"
On Error Resume Next
Set lookupRange = Application.InputBox("Select lookup table range", xTitleId, Type:=8)
lookupValue = Application.InputBox("Enter value to look up", xTitleId, Type:=2)
colNum = Application.InputBox("Enter return column number from the table", xTitleId, Type:=1)
rangeType = Application.InputBox("Exact match (FALSE) or Approximate match (TRUE)?", xTitleId, "FALSE", Type:=2)
If rangeType = "TRUE" Or rangeType = "true" Then
result = Application.WorksheetFunction.VLookup(lookupValue, lookupRange, colNum, True)
Else
result = Application.WorksheetFunction.VLookup(lookupValue, lookupRange, colNum, False)
End If
If IsError(result) Then
MsgBox "Lookup failed – no matching value found.", vbExclamation, xTitleId
Else
MsgBox "Found value: " & result, vbInformation, xTitleId
End If
End Sub2. Klicken Sie zum Ausführen auf die 
-Schaltfläche. Folgen Sie den Anweisungen in den Dialogfeldern, um Ihren Tabellenbereich auszuwählen, einen Wert einzugeben, die Nummer der Rückgabespalte anzugeben und festzulegen, ob ein exakter oder ungefährer Abgleich erfolgen soll (geben Sie FALSCH für exakt und WAHR für ungefähr an).
Sobald der Vorgang abgeschlossen ist, wird entweder der gefundene Wert angezeigt oder eine Benachrichtigung ausgegeben, falls keine Übereinstimmung vorliegt. Dieser Ansatz minimiert manuelle Fehler und eignet sich ideal für sich wiederholende Aufgaben. Sollten unerwartete Ergebnisse auftreten, überprüfen Sie die korrekte Bereichsauswahl, die Spaltennummerierung sowie die Konsistenz des Wertetyps (z. B. Zahl im Vergleich zu Text).
Tipp: Speichern Sie Ihre Arbeit stets, bevor Sie Makros ausführen oder bearbeiten. Für Massenabfragen können Sie das VBA-Skript ganz einfach erweitern, um eine Liste von Werten automatisch zu verarbeiten oder die Ergebnisse direkt in ein anderes Arbeitsblatt auszugeben.
Weitere verwandte VLOOKUP-Artikel:
- VLOOKUP und mehrere zugehörige Werte verketten
- Wie allgemein bekannt ist, hilft die VLOOKUP-Funktion in Excel dabei, einen Wert zu suchen und die zugehörigen Daten aus einer anderen Spalte zurückzugeben. Normalerweise liefert sie jedoch nur den ersten übereinstimmenden Wert, wenn mehrere passende Datensätze vorhanden sind. In diesem Artikel erfahren Sie, wie Sie mit VLOOKUP mehrere zugehörige Werte entweder in einer einzigen Zelle oder als vertikale Liste verketten können.
- VLOOKUP und Rückgabe des letzten übereinstimmenden Werts
- Wenn Sie eine Liste mit mehrfach vorkommenden Elementen haben und lediglich den letzten übereinstimmenden Wert zu Ihren angegebenen Kriterien ermitteln möchten, hilft Ihnen folgendes Beispiel: In Spalte A finden sich doppelte Produktnamen, während Spalte C unterschiedliche Namen enthält. Ihr Ziel ist es, für das Produkt „Apple“ den letzten zugehörigen Eintrag „Cheryl“ zurückzugeben.
- Werte mit VLOOKUP über mehrere Arbeitsblätter hinweg abrufen
- In Excel können Sie die VLOOKUP-Funktion problemlos nutzen, um übereinstimmende Werte innerhalb einer einzelnen Tabelle auf einem Arbeitsblatt abzurufen. Doch haben Sie sich schon einmal gefragt, wie Sie einen Wert über mehrere Arbeitsblätter hinweg nachschlagen können? Angenommen, ich habe die folgenden drei Arbeitsblätter mit Datenbereichen und möchte basierend auf einem bestimmten Kriterium die entsprechenden Werte aus diesen drei Arbeitsblättern abrufen.
- VLOOKUP und Rückgabe der gesamten Zeile / Ganze Zeile eines gefundenen Werts
- Normalerweise können Sie mithilfe der VLOOKUP-Funktion einen übereinstimmenden Wert aus einem Datenbereich abrufen – aber haben Sie schon einmal versucht, die gesamte Zeile mit Daten basierend auf einem bestimmten Kriterium zu finden und zurückzugeben?
- VLOOKUP über mehrere Blätter hinweg und Summierung der Ergebnisse
- Angenommen, Sie haben vier Arbeitsblätter mit identischem Format und möchten in der jeweiligen Spalte „Produkt“ nach „Fernseher“ suchen sowie die Gesamtbestellmenge aus allen Blättern ermitteln – wie im folgenden Screenshot dargestellt. Wie lässt sich diese Aufgabe in Excel einfach und schnell lösen?
Beste Office-Produktivitätstools
Verbessern Sie Ihre Excel-Kenntnisse mit Kutools für Excel und erleben Sie Effizienz wie nie zuvor.Kutools für Excel bietet über 300 erweiterte Funktionen zur Steigerung der Produktivität und Zeit sparen.Klicken Sie hier, um die Funktion zu erhalten, die Sie am dringendsten benötigen...
Office Tab bringt eine tabbasierte Oberfläche in Office und macht Ihre Arbeit viel einfacher
- Aktivieren Sie tabbasiertes Bearbeiten und Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Registerkarten desselben Fensters – statt jedes in einem separaten Fenster zu öffnen.
- Steigert Ihre Produktivität um 50 % und erspart Ihnen täglich Hunderte von Mausklicks!
Alle Kutools-Add-Ins – ein Installationsprogramm
Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook und PowerPoint sowie Office Tab Pro – ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- Alles-in-einem-Paket— Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro
- Ein Installationsprogramm, eine Lizenz— innerhalb weniger Minuten eingerichtet (MSI-fähig)
- Funktioniert besser zusammen— optimierte Produktivität über alle Office-Anwendungen hinweg
- 30-tägige Vollversion zum Testen— keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis— sparen Sie im Vergleich zum Kauf einzelner Add-Ins