Live-Forum - Die aktuellen Beiträge
Datum
Titel
20.10.2025 19:34:56
Eine Zelle in 3 Spalten teilen
20.10.2025 19:24:08
Excel Form befüllen
20.10.2025 19:03:51
wenn istleer funktioniert nicht wegen formel
20.10.2025 13:56:55
Wenn Funktion mit Xverweis oder ähnlich
Keine passende Antwort im Forumarchiv gefunden?
Stelle deine Frage im Live-Forum
Anzeige
Anzeige
HERBERS
Excel-Forum (Archiv)
20+ Jahre Excel-Kompetenz: Von Anwendern, für Anwender
Vorheriger Thread
Vorheriger Thread
Rückwärts Blättern
1588to1592
1588to1592
Aktuelles Verzeichnis
Verzeichnis Übersicht
Verzeichnis Übersicht
Übersicht Verzeichnisse
Nächster Thread
Nächster Thread
Vorwärts blättern
Inhaltsverzeichnis

sverweis mit wechselnder matrix

Forumthread: sverweis mit wechselnder matrix

sverweis mit wechselnder matrix
07.11.2017 00:40:35
thomas
woooow,
vorerst ein herzliches hallo,
dies scheint mir eine seite mit lauter schlauen leuten.
... trotz einger toller beiträge und lösungen, komm ich aber nicht auf die lösung meines problems.
meine ausgangslage IST FOLGENDE:
2 tabellen,
TABELLE 1: hat einen datenbereich von: D9:AJ49
in TABELLE 2,
(hab ich dank eures forums einen sverweis erstmalig zusammen gebracht)
ALS ERSTEN VERWEIS BENÖTIGE ich den Bereich D9:E49
UND funktioniert auch recht gut, da die entsprechenden daten (zahlen)ERSCHEINEN.
ich habe diese MATRIX in BENANNTEN BEREICH, unter bereich1530 abgespeichert.
deshalb sieht MEIN SVERWEIS wie folgt aus:
=WENNFEHLER(SVERWEIS(A5;bereich1530;2;FALSCH);"")
(DIE WENN-ABFRAGE ist, dass ich keine unschönen Fehlermeldungen erhalte, wenn nichts passendes gefunden wurde)
SOOOO und nun mein problem:
ICH würde nun eine einfache formel bzw EINE EINFACHE bedienung benötigen, dass jetzt bei der nächsten "abfrage" nun DIE MATRIX nicht mit Spalte D beginnt, somdern der nächsten.
(SPRICH SPALTE E; beim nächsten mal F UND SO WEITER ....)
Nun hab ich mir einen weiteren bereich angelegt (BEREICH1600; SO HEISST DIE NÄCHSTE SPALTE) DIESE IST NUN EBEN: E9:F49
BEREICH1630 wäre: F9:G49
UND SO WEITER .....
das ganze nützt mir aber nur bedingt etwas, da ich dennoch diesen namen (BEREICH1600) MANUEL in der formel(IM SVERWEIS) eingeben UND ändern müsste, unD in weitere folge dies auch nach unten auf weitere zellen kopieren müsste.
meine frage an die geschätztEn GURUS:
  • gibt es eine möglichkeit zb in einer freien zelle zb bereich1600 einzugeben, auf welches sich dann diE formel/der sverweis bezieht!?!?

  • oder gibt es gar eine "simplere" VARIANTE dass ich einfach steuern kann, ab welcher splalte die überprüfung stattfindet?

    • Bitte verzeiht mir die ausführliche beschreibung.
    ich will/wollt Euch halt nicht in der GLASKUGEL raten lassen.
    bin euch für jeden tip zur lösung sehr dankbar
    lg aus wien
    thomas
    SCREENSHOT DER TABELLE 1:
    Userbild
    screenshot der ausgabe um 15.30:
    Userbild
    SCREENSHOT DER AUSGABE UM 16:00
    Userbild
    Anzeige

    6
    Beiträge zum Forumthread
    Beiträge zu diesem Forumthread

    Betreff
    Datum
    Anwender
    Anzeige
    AW: sverweis mit wechselnder matrix
    07.11.2017 03:21:33
    Rainer
    Hallo Thomas,
    du kannst SVERWEIS(A3,D9:E49,2,falsch) ersetzen durch INDEX(E9:E49,VERGLEICH(A3,D9:D49,0)).
    Den Bereich kannst du über weitere INDEX Formeln variabel gestalten.
    z.B. INDEX(INDEX(1:1048576,9,5):INDEX(1:1048576,49,5),VERGLEICH(A3,INDEX(1:1048576,9,4):INDEX(1:1048576,49,4),0))
    Die Werte für Spalte (hier 4 oder 5) und Startzeile (9) bzw. Endzeile (49) kannst du auch als Zellverweis schreiben.
    Gruß, Rainer
    Anzeige
    AW: sverweis mit wechselnder matrix
    07.11.2017 03:24:44
    Rainer
    Noch ein Hinweis:
    du musst in den Formelbeispielen Komma(,) durch Semikolon(;) ersetzen, damit es im "deutschen" Excel funktioniert. Ich habe meins auf Komma umgestellt, weil ich parallel mit der englischen Version arbeite.
    AW: sverweis mit wechselnder matrix
    07.11.2017 10:37:09
    Luschi
    Hallo thomas,
    Dir ist schon klar, daß es sich hier um ein Excel-Forum handelt und nicht um eine Kunstgalerie, in der nur Bilder hängen; was sollen da die SCREENSHOT - hier zählen nur harte Fakten in Form einer Excel-Demo-Datei.
    Aber mache es nicht so wie anderen Schlaumeier, die die Screenshots in eine Exceldatei hineinprojezieren und dann behaupten, alles richtig gemacht zu haben.
    Gruß von Luschi
    aus klein-Paris
    Anzeige
    AW: sverweis mit wechselnder matrix
    07.11.2017 15:26:12
    thomas
    HELLO LUSCHI;
    Sorry, ich bin zum ersten mal hier und dachte mir dass mit screenshots DIE ERKLÄRUNG ein wenig verständlicher wird.
    ich werde mich IN ZUKUNFT hüten, dieses forum zur Ausstellung "MEINER GEMÄLDE" zu missbrauchen ;-))
    LG AUS WIEN
    THOMAS
    AW: sverweis mit wechselnder matrix
    07.11.2017 15:21:26
    thomas
    hallo rainer,
    was bist du denn für ein "wunder-wuzzi"!?!?
    ich möchte mich vielmals, für diese rasche und kompetente lösung bei DIR bedanken.
    ... und das in aller HERRGOTTSNACHT!!!
    ich habe gestern bis 7 uhr noch daran gearbeitet, da mir (MIT MEINEM WISSEN) NICHT GLEICH ALLES KLAR WAR.
    ich habe jetzt allerdings die zahlen (BETREFFEND ZEILEN UND SPALTEN) MIT $-ZEICHEN versehen, dass sich diese beim runterziehen nicht verändern bzw erhöhen. ICH hoffe dass ist korrekt so?
    da ich es bis dato nicht hinbekomme - den verweis auf die andere tabelle - habe ich es jetzt mal unter tabelle 1 geschrieben. also eben nur in einer tabelle!!!
    MUSS mich irgendwo schlau machen wie und wo ICH den "verweis" AUF EINE ANDERE TABELLE, oder gar auf eine andere DATEI(!!!) IN DEINE INDEX-FORMEL INTEGRIEREN KANN.
    ... und da du so ein WUNDERWUZZI bist, gestatte mir noch eine FRAGE:
    In den unteren screenshots (ZB 1530) sieht man, dass ich in jeder zeille eine zelle gefärbt habe.
    dies resultiert, weil in der letzen Zelle EINER ZEILE ein wert kleiner 1 (null) steht:
    ICH WÜRDE ABER GERNE DIE LETZTE GANZE ZAHL EINFÄRBEN;
    sprich die matrix d5:K22 DURCHSUCHEN OB die nächste (RECHTE) spalte/zahl KLEINER 1 ist;
    ODER EBEN durchsuchen ob spalte x/Y einen wert KLEINER 1 hat und dann die nebenzelle (in dem fall dann die linke) EINFÄRBEN.
    nochmals, vielen lieben dank für deine lösung!!!
    ich hab so eine freude, dass dies nun variabel einsetzbar ist!!! (MITTELS ZELLENVERWEIS)
    LG; THOMAS
    Anzeige
    AW: sverweis mit wechselnder matrix
    08.11.2017 02:05:48
    Rainer
    Hallo Thomas,
    einfaches Beispiel, wir bilden in Tabelle1 die Summe aus einem Bereich in Tabelle2:
    =SUMME(Tabelle2!B3:B7)
    oder mit INDEX: 
    
=SUMME(INDEX(Tabelle2!1:1048576,3,2):INDEX(Tabelle2!1:1048576,8,2))
    Wobei es keinen Unterschied macht, ob man da die Tabelle angibt oder andere Excelmappen:
    =SUMME(INDEX([Mappe2]Tabelle1!1:1048576,3,2):INDEX([Mappe2]Tabelle1!1:1048576,8,2))
    und nun mit INDIREKT, um variable Tabellennamen (hier aus Zelle B1) benutzen zu können:
    =SUMME(INDEX(INDIREKT("'"&B1&"'!1:1048576"),3,2):INDEX(INDIREKT("'"&B1&"'!1:1048576"),8,2))
    da kannst du in B1 auch Tabellennamen oder Pfade mit Tabellennamen schreiben.
    Wegen deiner anderen Frage, da gibt es Lösungen mit bedingter Formatierung. Aber die gehen nur, wenn die Spalte mit dem gesuchten Wert fix ist, also immer Spalte K. Dann machst du eine bedingte Formatierung, nutzt die Formeleingabe und schreibst in Zeile 1: 
    =$K1
    Diese Formatierung kannst du dann auf alle Zeilen weiterkopieren.
    Wenn aber immer erst ermittelt werden soll, welches die letzte Spalte ist, dann fällt mir spontan nur was mit VBA ein.
    In dem Fall erstelle aber bitte einen neuen Beitrag, das hat mit der eigentlichen Frage nichts mehr zu tun. Dann kann evtl. auch jemand aus deiner Zeitzone schneller helfen.
    Gruß,
    Rainer
    Anzeige
    ;
    Anzeige
    Anzeige

    Infobox / Tutorial

    SVERWEIS mit wechselnder Matrix in Excel

    Schritt-für-Schritt-Anleitung

    1. Vorbereitung der Daten: Stelle sicher, dass Du zwei Tabellen in Excel hast. Tabelle 1 enthält den Datenbereich (z.B. D9:AJ49) und Tabelle 2 wird zur Abfrage verwendet.

    2. Benennen der Bereiche: Benenne die Bereiche entsprechend. Zum Beispiel:

      • Bereich1530 für D9:E49
      • Bereich1600 für E9:F49
      • Bereich1630 für F9:G49 usw.

    3. SVERWEIS Formel erstellen: Verwende die SVERWEIS-Formel in Tabelle 2:

      =WENNFEHLER(SVERWEIS(A5;bereich1530;2;FALSCH);"")

      Diese Formel gibt Dir den Wert aus dem zweiten Spaltenbereich zurück, wenn er gefunden wird.

    4. Matrixwechsel implementieren: Um die Matrix dynamisch zu wechseln, kannst Du eine Zelle (z.B. B1) nutzen, um den Namen des Bereichs einzugeben. Verwende dann die INDIREKT-Funktion:

      =WENNFEHLER(SVERWEIS(A5;INDIREKT(B1);2;FALSCH);"")

      Hierbei wird die Matrix entsprechend dem Wert in B1 gewechselt.

    5. Formel runterziehen: Um die Formel für mehrere Zeilen zu verwenden, ziehe die Zelle mit der Formel nach unten. Achte darauf, dass Du die Matrixreferenzen fixierst, wenn nötig (z.B. durch Verwendung von $-Zeichen).

    Häufige Fehler und Lösungen

    • Fehler: #NAME?
      Lösung: Stelle sicher, dass die Bereichsnamen korrekt sind und keine Tippfehler enthalten.

    • Fehler: #NV
      Lösung: Dies bedeutet, dass der Suchwert nicht gefunden wurde. Überprüfe, ob der Wert tatsächlich in der Matrix vorhanden ist.

    • Matrix nicht dynamisch
      Lösung: Stelle sicher, dass Du die INDIREKT-Funktion korrekt anwendest und dass die Zelle, die den Bereichsnamen enthält, nicht leer ist.

    Alternative Methoden

    • INDEX und VERGLEICH statt SVERWEIS: Du kannst die Kombination von INDEX und VERGLEICH verwenden, um flexiblere Suchen durchzuführen. Dies ist besonders nützlich, wenn Du mehrere SVERWEIS-Operationen benötigst. Hier ein Beispiel:

      =INDEX(E9:E49;VERGLEICH(A3;D9:D49;0))

    • Bedingte Formatierung: Um visuelle Hinweise auf bestimmte Werte zu geben, kannst Du die bedingte Formatierung nutzen, um Zellen hervorzuheben, die bestimmten Kriterien entsprechen.

    Praktische Beispiele

    1. Wechsel der Matrix durch Zellreferenz:

      • Wenn Du den Namen des Bereichs in Zelle B1 eingibst, kannst Du die folgende Formel verwenden: 
        =WENNFEHLER(SVERWEIS(A2;INDIREKT(B1);2;FALSCH);"")

    2. Summe aus einem anderen Bereich:

      • Um eine Summe aus einem anderen Tabellenbereich zu bilden, kannst Du folgende Formel nutzen: 
        =SUMME(INDEX(Tabelle2!1:1048576;3;2):INDEX(Tabelle2!1:1048576;8;2))

    Tipps für Profis

    • Nutze die Funktion INDIREKT in Kombination mit SVERWEIS, um Bereiche dynamisch zu gestalten. Dies hilft besonders, wenn Du die Datenstruktur häufig änderst.

    • Achte darauf, die Matrix immer zu überprüfen, besonders wenn Du mit großen Datenmengen arbeitest (z.B. 1/1048576 in Excel).

    • Ziehe Formeln immer nach unten, um Zeit zu sparen, und verwende dabei die $-Referenzen, um die richtigen Zellen zu fixieren.

    FAQ: Häufige Fragen

    1. Kann ich SVERWEIS und INDEX gleichzeitig verwenden?
    Ja, Du kannst beide Funktionen kombinieren, um flexiblere Abfragen durchzuführen.

    2. Wie kann ich die letzte Spalte in einer Matrix dynamisch finden?
    Das Ermitteln der letzten Spalte erfordert oft VBA oder eine spezielle Formel. Eine einfache bedingte Formatierung kann genutzt werden, wenn die Spalte fix ist.

    3. Was ist der Unterschied zwischen SVERWEIS und VLOOKUP in Excel?
    Es gibt keinen funktionalen Unterschied; VLOOKUP ist der englische Begriff für SVERWEIS. Der Hauptunterschied liegt in der Sprache der Excel-Version.

    4. Wie viele Zeilen kann ich mit SVERWEIS abdecken?
    Excel hat eine maximale Zeilenanzahl von 1.048.576, was bedeutet, dass Du in der Theorie bis zu dieser Anzahl Zeilen abdecken kannst.

    Beliebteste Forumthreads (12 Monate)

    Jetzt das Excel-Portal bewerten: Deine Stimme zählt!
    Anzeige
    Anzeige
    Entdecke mehr
    Finde genau, was du suchst

    Die erweiterte Suchfunktion hilft dir, gezielt die besten Antworten zu finden

    Suche nach den besten Antworten
    Unsere beliebtesten Threads

    Entdecke unsere meistgeklickten Beiträge in der Google Suche

    Top 100 Threads jetzt ansehen
    Anzeige