Wie gibt man einen Wert zurück, wenn ein bestimmter Wert in einem bestimmten Bereich in Excel vorhanden ist?
Beim Arbeiten mit Daten in Excel ist es oft notwendig zu bestimmen, ob ein bestimmter Wert innerhalb eines definierten Bereichs vorhanden ist, und falls ja, einen Wert aus einer benachbarten Zelle abzurufen, der diesem Eintrag entspricht. Zum Beispiel, wie im linken Screenshot gezeigt, wenn Sie die Zahl 5 innerhalb einer Liste oder eines Bereichs suchen, möchten Sie möglicherweise automatisch den entsprechenden benachbarten Wert zurückgeben – in diesem Fall nützlich für Aufgaben wie das Nachschlagen von Produkt-IDs, das Abrufen von Benutzerinformationen oder das Zuordnen von Codes zu Werten ohne manuelle Suche.
Geben Sie einen Wert zurück, wenn ein bestimmter Wert in einem bestimmten Bereich vorhanden ist
Geben Sie einen Wert zurück, wenn ein bestimmter Wert in einem bestimmten Bereich mit der VLOOKUP-Funktion vorhanden ist
Um schnell einen Wert zu finden, der mit einem bestimmten Eintrag in einer Datentabelle oder einem Bereich in Excel verknüpft ist, bietet die VLOOKUP-Funktion eine einfache Lösung.
Diese Methode ist besonders effektiv, wenn Ihre Suchspalte (wo Sie nach dem Wert suchen) die am weitesten links stehende Spalte Ihres Datenbereichs ist und Sie Daten aus einer Spalte rechts davon zurückgeben möchten. Sie wird häufig zum Suchen von Codes, Namen, IDs oder Referenznummern verwendet und erleichtert das Abrufen verwandter Details.
1. Wählen Sie eine leere Zelle aus, in der das Ergebnis erscheinen soll. Geben Sie die folgende Formel in die Formel-Leiste ein:
=VLOOKUP(E2,A2:C8,3,TRUE)Drücken Sie die Eingabetaste, um die Formel auszuführen. Siehe Screenshot:
In diesem Beispiel, wenn die Zahl 5 (in Zelle E2) innerhalb des angegebenen Zahlenbereichs in Spalte A gefunden wird (zum Beispiel zwischen 4 und 6), sucht Excel diesen Wert und füllt sofort den entsprechenden 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, weil die Zahl 5 innerhalb des Bereichs 4-6 liegt.
Hinweis: In der Formel bezieht sich E2 auf den Suchwert, A2:C8 ist der Datenbereich, der die Suchwerte und die Rückgaben umfasst, und 3 gibt an, dass der Rückgabewert aus der dritten Spalte des angegebenen Bereichs stammen soll. Passen Sie diese Referenzen je nach Bedarf für Ihr Arbeitsblatt an.
Tipps & Stolperfallen:
- Stellen Sie sicher, dass der Suchbereich (A2:C8) sowohl die Suchspalte als auch die Rückgabespalte enthält.
- Wenn Sie VLOOKUP mit dem Argument WAHR verwenden, muss die Suchspalte in aufsteigender Reihenfolge sortiert sein, sonst erhalten Sie möglicherweise unerwartete Ergebnisse.
- Für exakte Übereinstimmungen verwenden Sie FALSCH als viertes Argument, aber für Bereichssuchen (wie in diesem Beispiel) lassen Sie es als WAHR.
- Wenn sich Ihre Daten häufig ändern, überprüfen Sie Ihre Referenzen doppelt, um Fehler durch Fehlausrichtungen zu vermeiden.
Geben Sie einen Wert zurück, wenn ein bestimmter Wert in einem bestimmten Bereich mit den INDEX- und VERGLEICH-Funktionen vorhanden ist
Die Kombination aus INDEX und VERGLEICH ist eine flexible Methode, um einen Wert zurückzugeben, wenn ein bestimmter Wert in einem bestimmten Bereich vorhanden ist. Im Gegensatz zu VLOOKUP können INDEX und VERGLEICH in jeder Spalte nach einem Wert suchen und ein Ergebnis aus einer beliebigen anderen Spalte zurückgeben, unabhängig von der Reihenfolge. Dies ist besonders nützlich, wenn Ihre Suchspalte nicht die am weitesten links stehende Spalte ist oder wenn Sie mehr Flexibilität in Ihrer Datenstruktur benötigen.
1. Wählen Sie eine leere Zelle aus, in der das Ergebnis erscheinen soll (zum Beispiel F2). Geben Sie die folgende Formel in die Formel-Leiste ein:
=INDEX(C2:C8, MATCH(E2, A2:A8,1))Drücken Sie die Eingabetaste, um die Formel zu bestätigen.
- VERGLEICH(E2, A2:A8, 1) sucht nach der Position des größten Werts ≤ E2 in Spalte A. (Dies erfordert, dass Spalte A in aufsteigender Reihenfolge sortiert ist.)
- INDEX(C2:C8, ...) gibt den Wert aus Spalte C an der Zeilennummer zurück, die von VERGLEICH gefunden wurde.
Diese Formel sucht den Wert in E2 innerhalb des Bereichs A2:A8. Wenn der Wert gefunden wird (zum Beispiel liegt 5 zwischen 4 und 6 in einer der Zeilen), gibt die Funktion VERGLEICH seine relative Position zurück, und INDEX holt den Wert aus der entsprechenden Zeile von C2:C8. Die '1' in VERGLEICH bedeutet eine ungefähre Übereinstimmung, also stellen Sie sicher, dass Ihr Suchbereich entsprechend sortiert ist.
- Wenn Sie eine exakte Übereinstimmung wünschen, verwenden Sie
0als drittes Argument in VERGLEICH. - INDEX und VERGLEICH unterstützen sowohl vertikale als auch horizontale Datenorientierung.
- Wenn der Wert nicht gefunden wird, gibt die Formel #NV zurück; erwägen Sie, sie mit WENNFEHLER zu umschließen, um freundlichere Ausgaben zu erhalten.
Geben Sie einen Wert zurück, wenn ein bestimmter Wert in einem bestimmten Bereich mit der XLOOKUP-Funktion vorhanden ist
Die XLOOKUP-Funktion ist eine moderne Alternative zur Wertsuche in Excel365 und Excel2019. XLOOKUP überwindet viele Einschränkungen von VLOOKUP, wie z.B. Einschränkungen bei der Position der Suchspalte und automatische genaue/ungefähr Übereinstimmungen.
1. Geben Sie in Ihrer gewünschten Ausgabezelle (zum Beispiel F2) die folgende Formel ein:
=XLOOKUP(1, (E2>=A2:A8)*(E2<=B2:B8), C2:C8)Nachdem Sie die Formel eingegeben haben, drücken Sie die Eingabetaste, 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 oder gleich jedem Wert in Spalte B ist.
- Das Multiplizieren dieser beiden Bedingungen erstellt ein Array von 1 und 0, wobei 1 bedeutet, dass E2 zwischen A und B in dieser Zeile liegt.
- XLOOKUP(1, ..., C2:C8) sucht nach der ersten 1 und gibt den entsprechenden Wert aus Spalte C zurück.
- XLOOKUP passt sich dynamisch an, wenn Spalten eingefügt oder verschoben werden, im Gegensatz zu VLOOKUP mit festen Spaltennummern.
- Funktioniert sowohl für vertikale als auch für horizontale Daten.
- Benötigt Excel 365 oder 2021; für ältere Versionen verwenden Sie die oben beschriebenen anderen Methoden.
Entfesseln Sie die Magie von Excel mit Kutools AI
- Intelligente Ausführung: Führen Sie Zellenoperationen durch, analysieren Sie Daten und erstellen Sie Diagramme – alles angetrieben durch einfache Befehle.
- Benutzerdefinierte Formeln: Erstellen Sie maßgeschneiderte Formeln, um Ihre Arbeitsabläufe zu optimieren.
- VBA-Codierung: Schreiben und implementieren Sie VBA-Code mühelos.
- Formelinterpretation: Verstehen Sie komplexe Formeln mit Leichtigkeit.
- Textübersetzung: Überwinden Sie Sprachbarrieren in Ihren Tabellen.
Verwandte Artikel:
- Wie sucht man mit vlookup einen Wert und gibt wahr oder falsch / ja oder nein in Excel zurück?
- Wie gibt man mit vlookup einen Wert in einer benachbarten oder nächsten Zelle in Excel zurück?
- Wie gibt man einen Wert in einer anderen Zelle zurück, wenn eine Zelle bestimmten Text in Excel enthält?
Die besten Produktivitätstools für das Büro
Stärken Sie Ihre Excel-Fähigkeiten mit Kutools für Excel und genießen Sie Effizienz wie nie zuvor. Kutools für Excel bietet mehr als300 erweiterte Funktionen, um die Produktivität zu steigern und Zeit zu sparen. Klicken Sie hier, um die Funktion zu erhalten, die Sie am meisten benötigen...
Office Tab bringt die Tab-Oberfläche in Office und macht Ihre Arbeit wesentlich einfacher
- Aktivieren Sie die Tabulator-Bearbeitung und das Lesen in Word, Excel, PowerPoint, Publisher, Access, Visio und Project.
- Öffnen und erstellen Sie mehrere Dokumente in neuen Tabs innerhalb desselben Fensters, statt in neuen Einzelfenstern.
- Steigert Ihre Produktivität um50 % und reduziert hunderte Mausklicks täglich!
Alle Kutools-Add-Ins. Ein Installationspaket
Das Kutools for Office-Paket bündelt Add-Ins für Excel, Word, Outlook & PowerPoint sowie Office Tab Pro und ist ideal für Teams, die mit mehreren Office-Anwendungen arbeiten.
- All-in-One-Paket — Add-Ins für Excel, Word, Outlook & PowerPoint + Office Tab Pro
- Ein Installationspaket, eine Lizenz — in wenigen Minuten einsatzbereit (MSI-kompatibel)
- Besser gemeinsam — optimierte Produktivität in allen Office-Anwendungen
- 30 Tage kostenlos testen — keine Registrierung, keine Kreditkarte erforderlich
- Bestes Preis-Leistungs-Verhältnis — günstiger als Einzelkauf der Add-Ins