SVERWEIS und BEREICH.VERSCHIEBEN in Excel
Schritt-für-Schritt-Anleitung
Um in Excel einen Wert aus einem anderen Sheet zu ziehen, der sich drei Spalten rechts und eine Zeile über dem Suchwert befindet, kannst du die Kombination von SVERWEIS und BEREICH.VERSCHIEBEN verwenden. Hier ist eine Schritt-für-Schritt-Anleitung:
-
Öffne Deine Excel-Datei und gehe zu dem Sheet, in dem du den Wert abfragen möchtest (z.B. Tabelle_1).
-
Wähle die Zelle aus, in der das Ergebnis erscheinen soll (z.B. B1).
-
Gib die folgende Formel ein:
=BEREICH.VERSCHIEBEN(Tabelle_2!A3;VERGLEICH(A1;Tabelle_2!A3:A40;0)-2;3;1;1)
Diese Formel sucht den Wert in A1 von Tabelle_1, sucht ihn in der Spalte A von Tabelle_2 und gibt den Wert drei Spalten rechts und eine Zeile darüber zurück.
-
Drücke Enter, um das Ergebnis zu sehen.
Häufige Fehler und Lösungen
-
Fehler: #NV
- Lösung: Stelle sicher, dass der Suchwert in der angegebenen Range vorhanden ist. Überprüfe auch den Bereich, der im SVERWEIS verwendet wird.
-
Fehler: #BEZUG!
- Lösung: Dies kann auftreten, wenn die Referenz in der BEREICH.VERSCHIEBEN-Formel ungültig ist. Überprüfe, ob die angegebenen Zeilen und Spalten korrekt sind.
-
Fehler: #WERT!
- Lösung: Überprüfe die Eingaben in der Formel. Möglicherweise sind falsche Zellreferenzen oder Datentypen verwendet worden.
Alternative Methoden
Falls du eine andere Methode zum Abrufen von Daten aus einem anderen Bereich verwenden möchtest, kannst du auch folgende Optionen in Betracht ziehen:
-
INDEX und VERGLEICH: Diese Kombination erlaubt es dir, flexibler mit dem Zugriff auf Daten zu arbeiten. Ein Beispiel wäre:
=INDEX(Tabelle_2!A1:E40;VERGLEICH(A1;Tabelle_2!A1:A40;0)-1;4)
-
XVERWEIS (in neueren Excel-Versionen verfügbar): Eine modernere Alternative zu SVERWEIS, die einfacher zu verwenden und flexibler ist.
Praktische Beispiele
Hier sind einige Beispiele, wie du die Formeln in verschiedenen Szenarien verwenden kannst:
-
Beispiel für BEREICH.VERSCHIEBEN:
Angenommen, du hast in Tabelle_2 folgende Daten:
- A3: Wert 1
- B3: Dummy 1/1
- C3: Dummy 1/2
- D3: Dummy 1/3
Wenn du in Tabelle_1 in Zelle A1 "Wert 1" eingibst, gibt die Formel in B1 den Wert "Dummy 1/3" zurück.
-
Beispiel für SVERWEIS:
Wenn du den Wert "Wert 2" suchst und den entsprechenden Dummy-Wert aus Tabelle_2 zurückgeben möchtest:
=SVERWEIS("Wert 2";Tabelle_2!A1:E40;4;FALSCH)
Tipps für Profis
-
Nutze benannte Bereiche, um die Formeln übersichtlicher zu gestalten. So kannst du anstelle von Zellreferenzen wie Tabelle_2!A3:A40 einfach Suchbereich verwenden.
-
Verwende die Funktion WENNFEHLER, um eine benutzerdefinierte Fehlermeldung anzuzeigen, falls die Suche keinen Treffer ergibt:
=WENNFEHLER(BEREICH.VERSCHIEBEN(...); "Wert nicht gefunden")
-
Halte deine Daten sauber und gut strukturiert, um die Nutzung von SVERWEIS und BEREICH.VERSCHIEBEN zu optimieren.
FAQ: Häufige Fragen
1. Was ist der Unterschied zwischen SVERWEIS und BEREICH.VERSCHIEBEN?
SVERWEIS wird verwendet, um Werte basierend auf einer Suchbedingung abzurufen, während BEREICH.VERSCHIEBEN es dir ermöglicht, einen bestimmten Bereich relativ zu einer Referenz zu verschieben.
2. Kann ich BEREICH.VERSCHIEBEN auch ohne SVERWEIS verwenden?
Ja, BEREICH.VERSCHIEBEN kann eigenständig verwendet werden, um Daten in einer Tabelle zu referenzieren, ohne eine Suchkriterium zu benötigen.
3. In welchen Excel-Versionen sind diese Funktionen verfügbar?
Beide Funktionen sind in den meisten modernen Excel-Versionen verfügbar, einschließlich Excel 2010 und neuer.