Wie gibt man einen Wert zurück, wenn ein vorgegebener Wert innerhalb eines bestimmten Bereichs in Excel vorhanden ist?
Beim Arbeiten mit Daten in Excel ist es oft erforderlich, zu prüfen, ob ein bestimmter Wert innerhalb eines definierten Bereichs vorhanden ist, und gegebenenfalls einen Wert aus einer benachbarten Zelle abzurufen, der diesem Eintrag entspricht. Wie im linken Screenshot dargestellt, möchten Sie beispielsweise die Zahl 5 innerhalb einer Liste oder eines Bereichs suchen und automatisch den zugehörigen benachbarten Wert zurückgeben – etwa zum Nachschlagen von Produkt-IDs, zum Abrufen von Benutzerinformationen oder zum Zuordnen von Codes zu Werten, ohne manuell suchen zu müssen.
Geben Sie einen Wert zurück, wenn ein vorgegebener Wert in einem bestimmten Bereich vorhanden ist
Geben Sie einen Wert zurück, wenn ein vorgegebener Wert in einem bestimmten Bereich vorhanden ist, mithilfe der VLOOKUP-Funktion
Um schnell einen Wert abzurufen, der einem bestimmten Eintrag in einer Datentabelle oder einem Bereich in Excel zugeordnet ist, bietet die VLOOKUP-Funktion eine einfache Lösung.
Diese Methode eignet sich besonders gut, wenn Ihre Suchspalte – also die Spalte, in der Sie den gesuchten Wert finden möchten – die linke Spalte Ihres Datenbereichs ist und Sie Daten aus einer Spalte rechts davon abrufen wollen. Häufig wird sie verwendet, um Codes, Namen, IDs oder Referenznummern zu suchen und zugehörige Details einfach abzurufen.
1. Wählen Sie eine leere Zelle aus, in der das Ergebnis angezeigt werden soll. Geben Sie die folgende Formel in die Bearbeitungsleiste ein:
=VLOOKUP(E2,A2:C8,3,TRUE)Drücken Sie Enter, um die Formel auszuführen. Siehe Screenshot:
In diesem Beispiel sucht Excel die Zahl 5 (in Zelle E2) innerhalb des angegebenen Zahlenbereichs in Spalte A – beispielsweise zwischen 4 und 6 – und fügt sofort den zugehörigen Wert aus der dritten Spalte (Spalte C) des Bereichs A2:C8 in Ihre ausgewählte Zelle ein. In der Abbildung wird „Addin 012“ zurückgegeben, da die Zahl 5 im Bereich von 4 bis 6 liegt.
Hinweis: In der Formel bezieht sich E2 auf den Suchwert, A2:C8 ist der Datenbereich, der sowohl den Suchbereich als auch die Rückgabewerte umfasst, und 3 legt fest, dass der Rückgabewert aus der dritten Spalte dieses Bereichs stammen soll. Passen Sie diese Bezüge entsprechend Ihrem Arbeitsblatt an.
Tipps & Fallstricke:
- Stellen Sie sicher, dass der Suchbereich (A2:C8) sowohl die Spalte mit den Suchwerten als auch die Spalte mit den Rückgabewerten umfasst.
- Wenn Sie VLOOKUP mit dem WAHR-Argument verwenden, muss die Suchspalte in aufsteigender Reihenfolge sortiert sein – andernfalls erhalten Sie möglicherweise unerwartete Ergebnisse.
- Verwenden Sie für exakte Übereinstimmungen FALSCH als viertes Argument, bei Bereichssuchen (wie in diesem Beispiel) jedoch WAHR.
- Wenn sich Ihre Daten häufig ändern, überprüfen Sie Ihre Bezüge erneut, um Fehlausrichtungen zu vermeiden.
Geben Sie einen Wert zurück, wenn ein vorgegebener Wert in einem bestimmten Bereich vorhanden ist, mithilfe der INDEX- und MATCH-Funktionen
Die Kombination aus INDEX und MATCH ist eine flexible Methode, um einen Wert zurückzugeben, sobald ein vorgegebener Wert in einem bestimmten Bereich gefunden wird. Im Gegensatz zu VLOOKUP kann INDEX und MATCH in jeder beliebigen Spalte nach einem Wert suchen und ein Ergebnis aus einer anderen beliebigen Spalte liefern – unabhängig von deren Position. Das ist besonders nützlich, wenn Ihre Suchspalte nicht die linke Spalte ist oder Sie mehr Flexibilität in Ihrer Datenstruktur benötigen.
1. Wählen Sie eine leere Zelle aus, in der das Ergebnis angezeigt werden soll (z. B. F2). Geben Sie die folgende Formel in die Bearbeitungsleiste ein:
=INDEX(C2:C8, MATCH(E2, A2:A8,1))Drücken Sie Enter, um die Formel zu bestätigen.
- MATCH(E2; A2:A8; 1) sucht die Position des größten Werts ≤ E2 in Spalte A. (Dazu muss Spalte A in aufsteigender Reihenfolge sortiert sein.)
- INDEX(C2:C8; ...) gibt den Wert aus Spalte C in der Zeile zurück, die von MATCH gefunden wurde.
Diese Formel sucht den Wert in E2 innerhalb des Bereichs A2:A8. Wird der Wert gefunden (z. B. liegt die 5 zwischen 4 und 6 in einer der Zeilen), gibt die MATCH-Funktion ihre relative Position zurück – und INDEX ruft den entsprechenden Wert aus C2:C8 ab. Die „1“ in der MATCH-Funktion steht für eine ungefähre Übereinstimmung; achten Sie daher darauf, dass Ihr Suchbereich entsprechend sortiert ist.
- Wenn Sie eine exakte Übereinstimmung benötigen, verwenden Sie
0als drittes Argument in MATCH. - INDEX und MATCH unterstützen sowohl vertikal als auch horizontal angeordnete Daten.
- Wenn der Wert nicht gefunden wird, gibt die Formel #NV zurück. Verwenden Sie zur besseren Lesbarkeit
WENNFEHLER.
Geben Sie einen Wert zurück, wenn ein vorgegebener Wert in einem bestimmten Bereich vorhanden ist, mithilfe der XLOOKUP-Funktion
Die XLOOKUP-Funktion ist eine moderne Alternative zur Wertesuche in Excel 365 und Excel 2019. Sie überwindet viele Einschränkungen von VLOOKUP – etwa die Abhängigkeit von der Position der Suchspalte sowie die automatische Auswahl zwischen exakter und ungefährer Suche.
1. Geben Sie in Ihre gewünschte Zielzelle (z. B. F2) die folgende Formel ein:
=XLOOKUP(1, (E2>=A2:A8)*(E2<=B2:B8), C2:C8)Drücken Sie nach der Eingabe der Formel Enter, um das Ergebnis in der ausgewählten Zelle anzuzeigen.
- (E2>=A2:A8) prüft, ob E2 größer oder gleich jedem Wert in Spalte A ist.
- (E2<=B2:B8) prüft, ob E2 kleiner als oder gleich jedem Wert in Spalte B ist.
- Durch Multiplikation dieser beiden Bedingungen entsteht ein Array aus 1 und 0, wobei 1 anzeigt, dass E2 in dieser Zeile zwischen A und B liegt.
- XLOOKUP(1; ...; C2:C8) sucht die erste „1“ und gibt den entsprechenden Wert aus Spalte C zurück.
- Im Gegensatz zu VLOOKUP mit festen Spaltennummern passt sich XLOOKUP dynamisch an, wenn Spalten eingefügt oder verschoben werden.
- Funktioniert sowohl mit vertikalen als auch mit horizontalen Daten.
- Erfordert Excel 365 oder 2021; für ältere Versionen nutzen Sie die oben beschriebenen Methoden.
Entfesseln Sie die Magie von Excel mit KUTOOLS AI
- Intelligente Ausführung: Führen Sie Zelloperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles ganz einfach per Sprachbefehl.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe optimal zu optimieren.
- VBA-Programmierung: Schreiben und implementieren Sie VBA-Code ganz mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln spielend leicht.
- Textübersetzung: Überwinden Sie Sprachbarrieren direkt in Ihren Tabellenkalkulationen.
Verwandte Artikel:
- Wie sucht man mit VLOOKUP einen Wert in Excel und gibt dafür WAHR oder FALSCH bzw. JA oder NEIN zurück?
- Wie sucht man mit VLOOKUP einen Rückgabewert in der benachbarten oder nächsten Zelle in Excel?
- Wie kann man in Excel einen Rückgabewert in einer anderen Zelle anzeigen, wenn eine Zelle einen bestimmten Text enthält?
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