Wie gleicht man mit VLOOKUP passende Werte in Excel von rechts nach links ab?
Die VLOOKUP-Funktion wird in Excel häufig zum Suchen und Abrufen von Daten verwendet. Eine ihrer Einschränkungen besteht jedoch darin, dass sie nur Werte Von links nach rechts abgleichen kann. Das bedeutet, dass sich der Suchwert in der ersten Spalte der Tabelle befinden muss und der Rückgabewert rechts von der Suchspalte liegen muss. Was aber, wenn Sie Werte von rechts nach links abgleichen müssen? In diesem Leitfaden werden verschiedene Methoden vorgestellt, um dies zu erreichen – sowohl formelbasierte als auch automatisierte VBA-Lösungen. Das Verständnis und die Anwendung dieser Techniken für die Suche von rechts nach links helfen Ihnen, real existierende Datenlayouts effektiv zu verarbeiten, die nicht immer den typischen Anforderungen von VLOOKUP entsprechen.
VLOOKUP-Werte von rechts nach links mit VLOOKUP- und WENN-Funktionen abgleichen
Obwohl VLOOKUP allein keine Suche von rechts nach links unterstützt, können Sie mithilfe der WENN-Funktion die Datenstruktur so anpassen, dass es dennoch funktioniert. Dieser Ansatz ist besonders praktisch, wenn Sie lieber bei vertrauten Excel-Funktionen bleiben und eine schnelle Lösung benötigen – ohne Ihren eigentlichen Datensatz umstrukturieren zu müssen.
Geben Sie die folgende Formel in die gewünschte Zelle ein und ziehen Sie anschließend den Ausfüllknauf auf alle Zellen, auf die Sie die Formel anwenden möchten, um sämtliche entsprechenden Werte zu erhalten. Dieser Ansatz eignet sich ideal für statische Bereiche, erfordert jedoch möglicherweise Anpassungen, sobald sich Ihre Daten erweitern oder ihre Struktur ändert. Siehe Screenshot:
=VLOOKUP(E2, IF({1,0}, $C$2:$C$9, $A$2:$A$9), 2, 0)
- E2: Dies ist der gesuchte Wert – der Schlüssel, nach dem Excel im begrenzten Bereich sucht.
- WENN({1,0}; $C$2:$C$9; $A$2:$A$9): Dieser Teil der Formel erstellt eine virtuelle Tabelle, indem die Spalten neu angeordnet werden. Normalerweise sucht VLOOKUP nur in der ersten Spalte einer Tabelle und gibt einen Wert aus einer Spalte rechts davon zurück. Mit WENN({1,0}; …) weisen Sie Excel an, eine neue Tabelle zu erzeugen, bei der die Spalten vertauscht sind:
♦ Die erste Spalte der neuen Tabelle ist $C$2:$C$9.
♦ Die zweite Spalte der neuen Tabelle ist $A$2:$A$9. - 2: Damit weist du VLOOKUP an, den Wert aus der zweiten Spalte der durch die WENN-Funktion erstellten virtuellen Tabelle zurückzugeben. In diesem Fall stammt der Wert aus dem Bereich $A$2:$A$9.
- 0: Dies legt fest, dass eine exakte Übereinstimmung erforderlich ist. Findet Excel in $C$2:$C$9 keinen exakten Treffer für E2, wird ein Fehler zurückgegeben.
Tipp: Falls ein #NV-Fehler angezeigt wird, überprüfen Sie sorgfältig, ob der Suchwert in Ihrer Suchspalte vorhanden ist. Dieser Ansatz eignet sich hervorragend für einfache (statische) Suchvorgänge, ist aber möglicherweise weniger geeignet für große dynamische Bereiche oder Situationen, in denen sich Größe oder Position der Tabelle häufig ändern.
VLOOKUP-Werte von rechts nach links mit Kutools für Excel abgleichen
Wenn Sie einen benutzerfreundlicheren Ansatz bevorzugen, bietet Kutools für Excel erweiterte Funktionen, die komplexe Suchvorgänge vereinfachen – etwa die Möglichkeit, mühelos von rechts nach links zu suchen. Kutools ist ideal für Anwender, die komplexe Formeln umgehen und schneller Zugriff auf erweiterte Suchoptionen ohne manuelle Einrichtung wünschen. Dieser Ansatz eignet sich hervorragend für Daten jeglicher Größe und für alle, die regelmäßig Suchvorgänge durchführen.
Nach der Installation von Kutools für Excelgehen Sie wie folgt vor:
1. Klicken Sie auf „Kutools“ > „Erweiterte SVERWEIS“ > „Von rechts nach links suchen“ (siehe Screenshot):
2. Führen Sie im Dialogfeld „Von rechts nach links suchen“ die folgenden Schritte aus:
- Wählen Sie die Zellen mit dem Suchwert und die Ausgabezellen in den Abschnitten „Platzierungsbereich der Liste“ und „Suchwertbereich“ aus;
- Geben Sie anschließend im Abschnitt „Datenbereich“ die entsprechenden Elemente an, und stellen Sie sicher, dass Ihre Bereiche korrekt sind, um Fehlanpassungen zu vermeiden.
- Klicken Sie zum Abschluss auf die Schaltfläche „OK“.
3. Die passenden Datensätze wurden nun basierend auf dem Suchwertbereich aus der rechten Liste zurückgegeben – siehe Screenshot:
Möchten Sie den Fehlerwert #NV durch einen eigenen Text ersetzen, klicken Sie einfach auf die Schaltfläche „Optionen“ und aktivieren Sie die Option „Ersetzen Sie das Ausgaberesultat, das nicht gefunden wird und ‚#N/A' zurückgibt, durch den angegebenen Wert“. Anschließend geben Sie Ihren gewünschten Text ein.
Diese Funktion ist besonders nützlich beim Teilen von Arbeitsblättern, da sie Fehlermeldungen in Ihren endgültigen Ausgaben vermeidet.
VLOOKUP-Werte von rechts nach links mit INDEX- und VERGLEICH-Funktionen abgleichen
Die Kombination aus INDEX und VERGLEICH ist eine vielseitige und leistungsstarke Alternative zu VLOOKUP: Sie ermöglicht die Suche nach Werten in jede Richtung – links, rechts, oben oder unten – und bietet so mehr Flexibilität, insbesondere wenn sich Ihre Suchspalte nicht in der ersten Spalte des Datenbereichs befindet. INDEX und VERGLEICH eignen sich besonders gut für große Datensätze und dynamische Tabellen, bei denen sich die Spaltenpositionen ändern können.
Geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie dorthin, um das Ergebnis anzuzeigen. Ziehen Sie anschließend den Ausfüllknauf nach unten auf alle Zellen, in denen die Formel erscheinen soll. Achten Sie darauf, dass die Bezugsbereiche Ihre gewünschten Daten vollständig abdecken, und setzen Sie absolute Bezüge (mithilfe des $-Zeichens), wenn die Suchbereiche beim Herunterkopieren der Formel unverändert bleiben sollen.
=INDEX($A$2:$A$9,MATCH(E2,$C$2:$C$9,0))
- E2: Dies ist der gesuchte Wert.
- MATCH(E2, $C$2:$C$9,0)Die VERGLEICH-Funktion sucht den Wert in E2 im Bereich $C$2:$C$9. Die 0 gibt an, dass eine exakte Übereinstimmung erforderlich ist. Wird der Wert gefunden, liefert die Funktion dessen relative Position innerhalb des Bereichs zurück.
- INDEX($A$2:$A$9, ...)Die INDEX-Funktion nutzt dann die von VERGLEICH gelieferte Position, um den passenden Wert im Bereich $A$2:$A$9 zu ermitteln.
Tipp: Wenn Sie Suchvorgänge über mehrere Spalten hinweg durchführen müssen, passen Sie einfach den INDEX-Bereich an oder erweitern Sie die Formel für mehrfache Bedingungsabgleiche. Besonders bei häufig aktualisierten Daten oder großen Tabellen ist die Kombination aus INDEX und VERGLEICH in der Regel robuster als VLOOKUP – denn so müssen Sie keine Spalten verschieben oder Suchtabellen neu erstellen.
Werte von rechts nach links mit der XLOOKUP-Funktion suchen
Wenn Sie Excel 365 oder Excel 2021 verwenden, ist die XLOOKUP-Funktion eine moderne und vereinfachte Alternative zu VLOOKUP: Sie sucht Werte in jede Richtung – ganz ohne komplexe Formeln. XLOOKUP ist ideal für alle, die einen übersichtlicheren und flexibleren Ansatz bevorzugen und mit der neuesten Excel-Version arbeiten. Im Gegensatz zu VLOOKUP oder HLOOKUP muss die Rückgabespalte nicht rechts der Suchspalte liegen, was deutlich mehr Flexibilität bietet.
Geben Sie die folgende Formel in eine leere Zelle ein oder kopieren Sie sie dorthin, um das Ergebnis anzuzeigen. Ziehen Sie anschließend den Ausfüllknauf nach unten auf alle Zellen, in denen diese Formel enthalten sein soll. Diese Methode eignet sich besonders gut für freigegebene Arbeitsmappen oder beim Erstellen von Vorlagen, die unter Umständen mit sich ändernden Datenstrukturen verwendet werden.
=XLOOKUP(E2,$C$2:$C$9,$A$2:$A$9)
- E2: Dies ist der gesuchte Wert.
- $C$2:$C$9Dies ist der Bereich, in dem Excel nach dem Wert in E2 sucht – das sogenannte Sucharray.
- $A$2:$A$9Dies ist der Bereich, aus dem Excel den entsprechenden Wert zurückgibt – das sogenannte Rückgabe-Array.
Hinweis: XLOOKUP ist in Excel-Versionen vor Excel 365 und Excel 2021 nicht verfügbar. Verwenden Sie in früheren Versionen stattdessen die oben beschriebene INDEX-&-VERGLEICH-Methode oder erwägen Sie Alternativen wie VBA.
Werte von rechts nach links mit einem VBA-Makro für automatisierte Suche von rechts nach links
In Szenarien, in denen formelbasierte Lösungen zu eingeschränkt sind oder repetitive Suchvorgänge automatisiert werden müssen, ermöglicht VBA (Visual Basic for Applications) leistungsstarke Suchvorgänge von rechts nach links – sogar mit komplexeren Kriterien und im Batch-Modus. Diese Methode erweist sich besonders als wertvoll bei großen Datensätzen, bei Automatisierungsbedarf oder bei Aufgaben, die sich mit herkömmlichen Formeln nur schwer umsetzen lassen. Die Nutzung von VBA setzt jedoch die Aktivierung von Makros sowie grundlegende Kenntnisse der Excel-Entwicklerumgebung voraus.
Anwendungsszenarien: Automatisierte Berichterstellung, Umgang mit dynamischen Bereichen, Arbeit mit nicht zusammenhängenden Daten sowie bei bedingten oder mehrstufigen Kriterien.
Vorteile: Sehr flexibel, wiederholbar und anpassbar.Nachteile: Erfordert die Aktivierung von Makros sowie grundlegende Skriptkenntnisse; ungeeignet für freigegebene Arbeitsmappen, in denen Makros eingeschränkt sind.
So verwenden Sie diese VBA-Lösung:
1. Klicken Sie auf Entwicklertools > Visual Basic, um den VBA-Editor zu öffnen. Klicken Sie im neuen Fenster Microsoft Visual Basic for Applications auf Einfügen > Modul, und fügen Sie den folgenden Code in das Modulfenster ein:
Sub RightToLeftVlookup()
Dim lookupValue As Variant
Dim searchRange As Range, returnRange As Range
Dim resultCell As Range
Dim foundCell As Range
On Error Resume Next
xTitleId = "KutoolsforExcel"
Set searchRange = Application.InputBox("Select the range to search (lookup column):", xTitleId, Type:=8)
Set returnRange = Application.InputBox("Select the range to return value from (target column):", xTitleId, Type:=8)
Set resultCell = Application.InputBox("Select the cell to output the result:", xTitleId, Type:=8)
lookupValue = Application.InputBox("Enter the value to look up:", xTitleId, Type:=2)
If searchRange Is Nothing Or returnRange Is Nothing Or resultCell Is Nothing Or lookupValue = "" Then
MsgBox "Operation cancelled.", vbExclamation
Exit Sub
End If
Set foundCell = searchRange.Find(lookupValue, LookIn:=xlValues, LookAt:=xlWhole)
If Not foundCell Is Nothing Then
Dim rowOffset As Long
rowOffset = foundCell.Row - searchRange.Rows(1).Row + 1
resultCell.Value = returnRange.Cells(rowOffset, 1).Value
Else
resultCell.Value = "#N/A"
End If
End Sub2. Um den Code auszuführen, schließen Sie nach dem Einfügen den VBA-Editor und kehren Sie zu Excel zurück. Drücken Sie dann die F5-Taste oder klicken Sie auf die Schaltfläche „Ausführen“.
3. Folgen Sie den Anweisungen, um Ihre Suchspalte (die Spalte, in der sich Ihr Suchwert befindet), die Rückgabespalte (die Spalte, aus der das Ergebnis stammen soll), die Ausgabezelle und den Suchwert selbst auszuwählen. Das Makro füllt Ihre Ergebniszelle dann automatisch mit dem passenden Wert aus der Rückgabespalte – selbst wenn diese links der Suchspalte liegt.
Fehlerbehebung & Tipps:
- Stellen Sie sicher, dass Suchbereich und Rückgabebereich dieselbe Anzahl an Zeilen haben und zeilenweise exakt übereinstimmen, um präzise Ergebnisse zu erhalten.
- Befinden sich Ihre Daten auf verschiedenen Blättern, wählen Sie bei der Aufforderung „Bereich auswählen“ entsprechend aus – achten Sie dabei jedoch auf die zeilenweise Ausrichtung.
- Wenn der Suchwert nicht gefunden wird, gibt das Makro „#NV“ in die von Ihnen angegebene Zelle zurück. Prüfen Sie dann Ihren Suchbereich und die Schreibweise des Suchwerts.
- Um das Makro auf mehrere Suchwertbereiche anzuwenden, können Sie es entweder so erweitern, dass es über einen Bereich von Werten hinweg Schleifen durchläuft, oder es für jeden benötigten Wert einzeln wiederholt ausführen.
- Damit der VBA-Code funktioniert, müssen Makros in Ihrer Arbeitsmappe aktiviert sein.
Dieses VBA-Makro bietet eine praktische Lösung, um Suchvorgänge von rechts nach links zu automatisieren und fortgeschrittene oder variable Kriterien zu verarbeiten – besonders dann, wenn Sie solche Aufgaben regelmäßig ausführen oder mehr Logik benötigen, als Formeln liefern können.
Obwohl VLOOKUP ein hervorragendes Werkzeug für einfache Suchvorgänge ist, hat es eine entscheidende Einschränkung: Es kann nur rechts der Suchspalte nach Werten suchen. Glücklicherweise gibt es jedoch mehrere alternative Methoden, um übereinstimmende Werte auch von rechts nach links in Excel zurückzugeben. Mit diesen Techniken finden und liefern Sie passende Werte schnell und effizient – unabhängig von deren Position in Ihrem Arbeitsblatt. Praktische Tipps hierfür: Überprüfen Sie Ihre Bereiche sorgfältig, stellen Sie sicher, dass exakte Übereinstimmungen verwendet werden, und wählen Sie stets die Methode, die am besten zu Ihrer Excel-Version und Ihren Arbeitsabläufen passt. Sollten Fehler wie #NV auftreten, vergewissern Sie sich, dass der gesuchte Wert tatsächlich vorhanden ist und Ihre Array-Größen korrekt übereinstimmen. Nutzen Sie VBA oder Add-Ins wie Kutools, speichern Sie Ihre Arbeit unbedingt und testen Sie die Funktion zunächst an Beispieldaten, bevor Sie sie in wichtigen Tabellen einsetzen. Wenn Sie noch mehr Excel-Tipps und -Tricks entdecken möchten,bietet unsere Website Tausende von Anleitungen.
Weitere verwandte Artikel:
- VLOOKUP-Werte über mehrere Arbeitsblätter hinweg
- In Excel können wir die VLOOKUP-Funktion problemlos anwenden, um passende Werte innerhalb einer einzelnen Tabelle eines Arbeitsblatts zurückzugeben. Aber haben Sie sich jemals überlegt, wie man Werte über mehrere Arbeitsblätter hinweg abgleichen kann? Angenommen, ich habe die folgenden drei Arbeitsblätter mit jeweils einem Datenbereich und möchte nun basierend auf bestimmten Kriterien entsprechende Werte aus diesen drei Arbeitsblättern abrufen.
- Exakte und ungefähre Übereinstimmung mit VLOOKUP in Excel verwenden
- In Excel ist VLOOKUP eine der wichtigsten Funktionen, um einen Wert in der äußerst linken Spalte einer Tabelle zu suchen und den entsprechenden Wert aus derselben Zeile eines angegebenen Bereichs zurückzugeben. Wenden Sie die VLOOKUP-Funktion in Excel bereits erfolgreich an? In diesem Artikel erfahren Sie, wie Sie VLOOKUP richtig nutzen.
- VLOOKUP – passenden Wert von unten nach oben in Excel abgleichen
- Normalerweise hilft Ihnen die VLOOKUP-Funktion dabei, von oben nach unten den ersten übereinstimmenden Wert aus einer Liste zu finden. Manchmal müssen Sie jedoch von unten nach oben suchen, um den letzten zugehörigen Wert zu extrahieren. Haben Sie gute Ideen, wie Sie diese Aufgabe in Excel lösen können?
- VLOOKUP und ganze Zeile / Ganze Zeile eines übereinstimmenden Werts in Excel zurückgeben
- Normalerweise können Sie mit der VLOOKUP-Funktion einen passenden Wert aus einem Datenbereich abrufen – aber haben Sie schon einmal versucht, eine ganze Zeile basierend auf bestimmten Kriterien zu finden und zurückzugeben, wie im folgenden Screenshot gezeigt?
- VLOOKUP und mehrere zugehörige Werte in Excel verketten
- Wie allgemein bekannt ist, hilft Ihnen 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 Treffer, wenn mehrere Übereinstimmungen vorliegen. In diesem Artikel zeige ich Ihnen, wie Sie mit VLOOKUP mehrere passende Werte entweder in einer einzigen Zelle oder als vertikale Liste verketten können.
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