Dass die Kombination der beiden Funktionen INDEX und VERGLEICH einem SVERWEIS um Längen voraus ist, habe ich vor langer Zeit schon in diesem Artikel beschrieben.
Wenn du bisher um diese zwei Funktionen einen großen Bogen gemacht oder sie vielleicht noch gar nicht gekannt hast, empfehle ich dir, dich ein wenig mit ihnen zu beschäftigen. Es lohnt sich wirklich, denn zusammen sind sie fast unschlagbar!
Einen kniffeligen Anwendungsfall dafür zeige ich dir in den nächsten beiden Artikeln. Es geht um die folgende Aufgabe:
Für einen kleinen Report sollen die benötigten Daten aus einer großen Rohdatentabelle per Formel ausgelesen werden. Die Schwierigkeit dabei ist, dass die jeder auszulesende Wert vier verschiedene Kriterien erfüllen muss.
Das war ist dir noch zu abstrakt? Die folgenden Bilder machen es deutlicher.
Die Ausgangslage
Mein kleiner Report soll immer einen Auszug aus einer Rohdatentabelle liefern. Die Beispieldatei zu diesem Artikel kannst du dir hier herunterladen.
Jeder einzelne Wert hängt somit von folgenden Kriterien ab:
- Produkt
- Merkmal (Umsatz oder Gewinn)
- Monat
- Ist-Wert / Vorjahreswert / Plan-Wert
Die noch leere Reporting-Tabelle
Die Rohdatentabelle hat dabei folgenden Aufbau: Jedes Produkt wird in einer Zeile dargestellt. Von links nach rechts kommen dann 12-Monatsblöcke mit den Ist-Werten, den Vorjahreswerten und den Plan-Werten. Im Bereich der Spalten B bis AK werden die Umsätze dargestellt, in den Spalten AL bis BU schließen sich nach dem gleichen Schema die Gewinne an.
Die Rohdatentabelle, Teil 1
Die Rohdatentabelle, Teil 2
Insgesamt also eine seeehr breite Tabelle, aus der wir die Daten auslesen wollen.
Du könntest vielleicht auf die Idee kommen, das Problem mit einer Pivot-Tabelle zu lösen (kleine Werbeunterbrechung für mein Buch: Pivot-Tabellen für Dummies). Aufgrund des Aufbaus meiner Beispieltabelle ist das aber keine Option, denn dabei handelt es sich ja bereits um eine Kreuztabelle.
Darum möchte ich dir eine Formel-Lösung mit den eingangs erwähnten Funktionen INDEX und VERGLEICH zeigen.
Funktionsweise von INDEX und VERGLEICH
Zunächst schauen wir uns die „normale“ Funktionsweise dieser beiden Tabellenfunktionen an, bevor es dann ans Eingemachte geht.
=INDEX(Matrix;Zeile;[Spalte])
Damit wird ein Wert aus einer Tabelle (= erstes Argument) zurückgeliefert, dessen Position durch die beiden folgenden Argumente Zeile und/oder Spalte bestimmt wird.
So liefert beispielsweise
=INDEX(A1:A20;5)
den Wert in der fünften Zeile im Bereich A1:A20 zurück. Also den Wert aus Zelle A5.
=INDEX(A1:G20);3;5)
liest den Wert am Schnittpunkt in der dritten Zeile und der fünften Spalte in A1:G20, also den Inhalt von Zelle E3.
=VERGLEICH(Suchkriterium;Suchmatrix;[Vergleichstyp])
Mit dieser Funktion wird in einer Tabelle („Suchmatrix“) nach dem im ersten Argument „Suchkriterium“ angegebenen Wert durchsucht. Über den Vergleichstyp wird festgelegt, auf welche Weise dieses Suchkriterium mit der Suchmatrix abgeglichen wird. Dafür können die Werte 1, 0 oder -1 angegeben werden. In den allermeisten Fällen wird man hier 0 (den Wert Null) verwenden, der nach einer exakten Übereinstimmung sucht.
Als Ergebnis wird jedoch nicht der Wert zurückgeliefert (denn der steht ja mit dem Suchkriterium bereits fest), sondern die Position dieses Wertes in der angegebenen Suchmatrix:
Die VERGLEICH-Funktion
Die Mächtigkeit dieser auf den ersten Blick unscheinbaren Funktionen ergibt sich aus ihrer Kombination. Die INDEX-Funktion benötigt für die Zeilen- und Spaltenangabe immer einen numerischen Wert. Dieser Wert ist im Normalfall jedoch nicht bekannt, sondern wir kennen ja lediglich ein Suchkriterium. Und damit kommt die VERGLEICH-Funktion ins Spiel, die zu diesem Kriterium den benötigten Positionswert zurückliefert.
Nach dieser kleinen Theorie geht’s jetzt an die Praxis.
Das Dreamteam INDEX und VERGLEICH
Beginnen wir ganz einfach. In unserer Reporting-Tabelle wollen wir zunächst nur die Ist-Umsätze für die beiden Kriterien „Produkt“ und „Monat“ berücksichtigen und damit die Rohdatentabelle durchsuchen. Alles andere interessiert uns vorerst noch nicht.
Bezogen auf meine Beispieltabelle lautet die Formel hierfür:
=INDEX($B$23:$BU$37;VERGLEICH($B$1;$A$23:$A$37;0);VERGLEICH($A5;$B$20:$BU$20;0))
INDEX und VERGLEICH kombiniert
Das erste Argument in der INDEX-Funktion ist der Wertebereich in meiner Rohdatentabelle, also B23:BU37, denn hier befindet sich ja irgendwo mein gesuchtes Ergebnis (ich habe gleich den kompletten Bereich bis BU verwendet, auch wenn uns zunächst die Spalte M gereicht hätte). Wie man im Bild erkennen kann, wird zur Bestimmung des Zeilen- und Spaltenindex jeweils eine VERGLEICH-Funktion verwendet.
Die erste VERGLEICH-Funktion sucht nach dem Produkt, das ich in Zelle B1 ausgewählt habe. Dabei wird die Produktliste A23:A37 durchsucht und die Position der Trefferzelle innerhalb dieser Liste angegeben. Das dritte Argument 0 (Null) sorgt dafür, dass nur ein exakter Treffer geliefert wird. Da sich das Produkt 1 an der ersten Stelle von A23:A37 befindet, wird hier der Wert 1 zurückgegeben.
Die zweite VERGLEICH-Funktion durchsucht die Monatsspalten B20:BU20 nach dem Monat, der sich in Zelle A5 befindet. Wie du hier siehst, kann man mit dieser Funktion sowohl Zeilen als auch Spalten durchsuchen. Da sich der Monat „Jan“ im angegebenen Spaltenbereich ebenfalls an erster Stelle befindet, liefert auch diese Funktion als Ergebnis 1 zurück.
Damit hat INDEX alles was es braucht, um den gewünschten Umsatz zu finden:
=INDEX($B$23:$BU$37;1;1)
Wenn du jetzt auch die Dollar-Zeichen genau wie von mir angegeben gesetzt hast, kannst Du die Formel nach unten kopieren und bekommst für jeden Monat den entsprechenden Wert. Nun kannst du die Formel testen, indem du in B1 ein anderes Produkt auswählst.
Das Dreamteam im Einsatz
Wie bekommt man es jetzt aber hin, die entsprechenden Vorjahres- und Planwerte zu bestimmen? Eine Variante wäre, jeweils den Bereich einzupassen, der als erstes Argument in der INDEX-Funktion verwendet wird. Also für die Ist-Werte beispielsweise:
=INDEX($N$23:$Y$37;VERGLEICH($B$1;$A$23:$A$37;0);VERGLEICH($A5;$B$20:$BU$20;0))
Die beiden VERGLEICH-Funktionen könnten bei unserem Tabellenaufbau unverändert bleiben und wir würden damit das gewünschte Ergebnis erreichen:
Variante mit angepasstem Wertebereich
Aber das wäre etwas lästig, denn dann muss für die Planwerte erneut der Suchbereich angepasst werden. Und wenn wir im nächsten Schritt auch noch über das Feld B2 zwischen Umsatz und Gewinn unterscheiden wollen, würden wir spätestens dann an die Grenzen stoßen.
Wie man dieses Problem löst, zeige ich Dir im nächsten Artikel. Dort werden wir die Verschachtelung der beiden Funktionen eine Stufe weitertreiben. Habe also noch etwas Geduld!
P.S. Die Lösung ist immer einfach. Man muss sie nur finden.
(Alexander Solschenizyn)
P.P.S. Das Problem sitzt meistens vor dem Computer.
Hallo Martin,
wie immer Klasse, sehr schön nachvollziehbar und verständlich erklärt. Ich freue mich auf die Folgeartikel.
Hallo Hans-Georg,
vielen Dank! Auf den Folgeartikel kannst Du durchaus gespannt sein, der wird nochmal eins oben drauf setzen 🙂
Schöne Grüße,
Martin
Ganz fieser Cliffhanger! 😀
Danke für den Artikel. So langsam wird INDEX und VERGLEICH mein geliebtes S- und WVERWEIS ablösen.
Sehe ich das richtig, dass VERGLEICH nur mit einer einspaltigen oder einzeiligen Matrix arbeiten kann? Es wundert mich, dass es keine direkte Funktion gibt, welche gleich Spalte als auch Zeile ausspuckt.
Schöne Grüße
Hallo Tim,
Cliffhanger klingt gut, fast wie ein Excel-Thriller 🙂
Ja, die VERGLEICH-Funktion kann man nur auf einspaltige oder einzeilige Bereiche anwenden. Eine kombinierte Funktion wäre in der Tat eine interessante Idee. Vielleicht kommt ja so etwas mal mit Excel 2025…
Schöne Grüße,
Martin
Hallo Martin,
ich arbeite derzeit auch an einer Auswertung einer sehr großen Rohdaten-Tabelle mittels einer INDEX-VERGLEICH Kombination. Diese hat in den Kopfzeilen 2 Kriterien und mindestens 5 Kriterien in den Anfangsspalten. Zu der Frage, ob VERGLEICH nur einspaltig/-zeilig funktioniert (in der Hoffnung sie richtig verstanden zu haben), glaube ich , dass es auch mit mehreren geht. Ich habe, um die eindeutige Position in meinen Kopfzeilen bei 2 Kriterien zu bestimmen, im hinteren Teil der INDEX-Funktion (SPALTE, wofür ich VERGLEICH verwende) einfach das Kaufmanns-„UND“ verwendet (&). Z.B. so: VERGLEICH(A1&A2;Rohdaten!$1:$1&Rohdaten!$2:$2;0).
Viele Grüße
Robert
Hallo Robert,
vielen Dank für den Hinweis. In der beschriebenen Form hast Du natürlich Recht, zwei oder mehr Zellen lassen sich über das Und-Zeichen verknüpfen und damit mehrere Kriterien kombinieren. Da habe ich mich tatsächlich etwas unklar ausgedrückt. Ich wollte in meinem vorhergehenden Kommentar nur sagen, dass man nicht einen echten mehrspaltigen Tabellenbereich durchsuchen kann. Denn durch die den Einsatz des Und-Zeichens die betreffenden Zeilen/Spalten ja faktisch immer zu einer einzigen zusammengefasst.
Schöne Grüße,
Martin
Hallo Martin,
verfasst du auch Artikel über Power Pivot? Das ist für mich noch ein neues aber sehr spannendes Thema.
Viele Grüße Daniel
Hallo Daniel,
zu Power Pivot kann ich leider nichts schreiben, da es in meiner Excel-Version nicht enthalten ist und ich daher keine Erfahrung damit habe. Leider ist die Lizenzpolitik von Microsoft an dieser Stelle etwas schwer nachvollziehbar…
Schöne Grüße,
Martin
BOW Martin, hartes Brot, aber super, auf den Gedanken bin ich noch nicht gekommen, quäle mich oft mit so großen Dateien.
Super Tipp, Danke LG Holger
Hallo Holger,
dann freu‘ Dich schon mal auf den zweiten Teil 🙂
Schöne Grüße,
Martin
…als mehr oder minder regelmäßiger Leser des Blogs konnte ich den Cliffhanger emotional nicht aushalten, und habe mich an einer Lösung versucht. Und siehe da, es ist mir gelungen, eine kopierbare Formel zu basteln, die aus der exorbitant breiten Tapete, die gewünschten Zahlen in der vorgegebenen Matrix darstellt. Dem geschätzten Betreiber dieses Blogs werde ich meinen Lösungsansatz per Mail zukommen lassen, um an dieser Stelle nicht zu spoilern. Trotzdem bin ich auf den Ausgang des Films höchst gespannt…
Hallo Matthias,
hahaha, der Artikel entwickelt sich offensichtlich zum echten Krimi 🙂
Meine Auflösung (natürlich wird der Mörder überführt) gibt es morgen.
Schöne Grüße,
Martin
Moin,
super Anleitung und gute Sache, macht es sehr einfach.
Ich habe die Formel auf einen ähnlichen Fall übertragen.
Hier sieht es aber so aus.
1. Zeile: MonatJahr, MonatJahr, MonatJahr, MonatJahr
2. Zeile: act,act,ytd,ytd
3. Zeile: Plan, Ist, Plan, Ist
Die Formel klappt in meiner ersten Eingabe. Wenn ich sie aber in die nächste Zeile kopiere, schmeisst die Formel leider einen Bezugsfehler?
VG
Jakob
Hallo Jakob,
das ist aus der Ferne natürlich schwer zu sagen. Aber wenn’s in der ersten Zeile geklappt hat, vermute ich mal, dass nur irgendwo die Bezüge nicht richtig gesetzt sind ($-Zeichen).
Schöne Grüße,
Martin
Hi,
alles super verständlich erklärt.
Gibt es schon den nächstfolgenden Artikel.
Habe nämlich ein Problem mit Tabellendaten auslesen.
Aus Tabelle 1 (Datenerfassungstabelle) gibt es Merkmal1 mehrmals, Merkmal 2 nur einmal. Nun soll alles in einer Zeile zusammengefasst werden.
Hoffe der nächste Artikel kann mir helfen.
Gruß PHM
Hallo,
alles super erklärt. Nur leider ergibt sich bei mir in der einen Zeile ein #Bezugfehler.
Sie können mir nicht zufällig dabei helfen? 🙂
Hallo Katharina,
da hier niemand den Aufbau Ihrer Tabelle kennt, wird das aus der Ferne etwas schwierig…
Wenn die Formel also in bestimmten Zeilen funktioniert und in anderen einen Bezug-Fehler liefert, haben Sie vermutlich die $-Zeichen nicht an der richtigen Stelle gesetzt, so dass sich beim Kopieren der Formel die Bezüge falsch anpassen.
Sie könne das relativ leicht erkennen, wenn Sie auf auf die fehlerhafte Formel gehen und mit F2 in den Bearbeitungsmodus schalten. Dann werden ja die Zellen farbig hervorgehoben, auf die die Formel zugreift. Vielleicht fällt Ihnen so der Fehler auf.
Schöne Grüße,
Martin
Hallo Martin,
ich habe das Problem, dass ich die Index+Vergleich-Formel auf die Ergebnisse einer Formel (RANG.GLEICH beinhalten das Suchkriterium, Ergebnisbereich „=A1“) anwenden möchte. Wie ich es auch drehe #NV. Hast du einen Trick 17 auf Lager? Die Daten vorher (nur Inhalte) woanders hinzukopieren wäre sehr uncool.
Danke im Voraus!
Hallo Martin,
ich habe folgende Tabelle:
E2 = aktueller Monat (z, B. 5 für Mai)
A5:A8 = In diese Felder soll in Abhängigkeit des Monats (E2) ein Eintrag aus einer nebenstehenden Tabelle (E5:G13) erfolgen.
E5 = eine 1 für Januar / G5 = z. B. Papier bestellen
E6 = eine 5 für Mai / G6 = z. B. Blumen umtopfen
E7 = eine 5 für Mai / G7 = z. B. 3 Säcke Blumenerde einkaufen
Ich habe inzwischen alle möglichen Formeln ausprobiert. Leider lande ich immer wieder an dem Punkt, an dem in E6 und in E7 der Text aus G6 erscheint. Ich möchte aber in E7 auch den Text aus G7 sehen. Und zwar dann, wenn ich in E2 = 5 eingebe.
Funktioniert hier INDEX überhaupt, wenn nach zwei oder mehr gleichen Kriterien gesucht werden soll?
Hallo Manuela,
im Artikel oben ist ja beschrieben, dass man INDEX auch verwenden kann, wenn nach mehreren Kriterien gesucht werden soll. Vielleicht hilft auch eine spezielle Variante des SVERWEIS weiter, wie ich es hier beschrieben habe:
https://www.tabellenexperte.de/super-sverweis/
Allerdings kann ich keinen konkreten Tipp geben, da ich aus deiner Tabellenbeschreibung oben nicht ganz schlau werde…
Schöne Grüße,
Martin
Hallo Martin,
kann ich Dir meine Tabelle irgendwie zukommen lassen? Ich kann hier nix zum uploaden finden …
Grüße
Manuela
Hallo Manuela,
im Impressum steht eine E-Mail-Adresse, da kannst Du mir die Datei hinschicken und ich werde einen kurzen Blick darauf werfen.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für deine Anleitung, hat mir sehr geholfen. Jetzt stehe ich allerdings vor einem anderem Problem damit.
Wenn ich eine Formel mit INDEX und 2 x Vergleich erstelle und dabei in eine anderes Tabellenblatt gehe (mit beiden vergleichen ins selbe Tabellenblatt) erhalte ich immer eine Fehlermeldung “ Bezug“.
Kann es sein, das mit 2 Vergleich nur im selben Tabellenblatt funktioniert?
Vielen lieben Dank für eine Antwort.
Beste Grüße
Bernd
Hallo Bernd,
nein, es spielt eigentlich keine Rolle, ob du die Formel in einem anderen Arbeitsblatt ausgibst. Du musst nur darauf achten, dass du in allen Bezügen auch tatsächlich auf das richtige Arbeitsblatt verweist. Vermutlich hat sich hier ein Fehler eingeschlichen.
Schöne Grüße,
Martin
Kann ich damit auch folgendes Problem lösen:
Eine Spalte zum Ankreuzen (=x eintragen), zweite Spalte mit Datumswerten.
Gesucht: das x mit dem ältesten Datum
Daaaank!
Hallo Hannelore,
dafür würde ich eine andere Lösung vorschlagen. Angenommen, die Datumswerte stehen in A1:A20 und die Kreuzchen in B1:B20:
=MAX((A1:A20)*(B1:B20=“x“))
Oder wenn du Office 365 einsetzt (oder Excel 2019), dann geht auch das:
=MAXWENNS(A1:A20;B1:B20;“x“)
Schöne Grüße,
Martin
Guten Tag Herr Weiß.
Mit der Indexfunktion versuche ich zurzeit folgendes Problem zu lösen:
Ich habe zwei Spalten. In der Spalte A trage ich fortlaufend eine Stundenanzahl ein. In der Spalte B in unregelmäßigen Abständen immer das Wort „ja“. Sprich, zu jeder Stundenanzahl in Spalte A gibt es in unregelmäßigen Abständen ein „ja“. Nun möchte ich einer separaten Zelle die Stundenanzahl anzeigen lassen, die zum letzten eingetragenen „ja“ gehört. Mein Problem besteht jetzt darin, dass ich zwar den letzten Eintrag auslesen kann, aber die dazugehörige Zeile, nicht dazu nutzen kann, den Stundeneintrag zu ermitteln, da sich die Funktion ja nur auf eine Spalte bezieht. Wüssten Sie eine geeignete Lösung?
Mit freundlichen Grüßen
Maik
Hallo Herr Reinhardt,
Sie müssen dazu die Zeilennummer bestimmen, in der das letzte „ja“ steht und über diese Zeilennummer mit INDEX den Wert aus der Stundenspalte auslesen.
Angenommen, in A1:A20 stehen die Stunden und B1:B20 die „ja“. Dann ermitteln Sie die letzte Zeilennummer so:
=MAX(ZEILE(B1:B20)*(B1:B20=“ja“))
Und dann das Ganze in die INDEX-Formel übernommen, um die Stunden zu liefern:
=INDEX(A1:A20;MAX(ZEILE(B1:B20)*(B1:B20=“ja“)))
Für den Fall, dass die Tabelle nicht in Zeile 1, sondern beispielsweise in Zeile 3 beginnt, muss die Anzahl der Zeilen oberhalb der Tabelle noch abgezogen werden:
=INDEX(A3:A23;MAX(ZEILE(B3:B23)*(B3:B23=“ja“))-2)
Schöne Grüße,
Martin
Hallo Martin,
meinen „Index(…..) Fehler“ habe ich jetzt selbst gefunden. In der Funktion setzte ich oft die absolute Spaltennummer ein. In der Funktion muss aber die Anzahl von Spalten vom Beginn der Liste/Bezug eingesetzt werden. In den Beispielen kommt dieser „Fehler“ nie vor, da die Liste meistens am Anfang steht. Man lernt doch nie aus. Wird dieser Punkt explizit irgendwo erwähnt?
Mit freundlichen Grüßen
Harald
Hallo Harald,
genau das war auch meine Antwort:
https://www.tabellenexperte.de/excel-dream-team-index-verweis/
Der Punkt wird im Artikel nicht explizit erwähnt, aber du findest ihn im Zweifel auch in der Hilfe-Funktion zu INDEX.
Schöne Grüße,
Martin
Vielen Dank für den tollen Artikel. Ich hatte mich um einzelnes Zahlenfeld über Spalten und Zeilen abzufragen mit SUMMENPRODUKT abgemüht, aber die Datenbasis hatte zwischen den Zahlen auch Text und ich kam nicht weiter. Deine Lösung hat wunderbar gepasst. Die Darstellung deines Beispiels ist anwenderfreundlich und lässt sich leicht in der eigenen Datei nachbauen. Viele Grüße, Jessica
Hallo Jessica,
freut mich, wenn der Artikel geholfen hat und danke für das schöne Feedback!
Schöne Grüße,
Martin
Ich habe ein Problem. Ich habe 1 Tabelle wo ich die Suche gemacht habe. Also per Sverweis holt ich mir meine Adresse von den Anbietern aus Tabelle 1. Soweit so gut. Klappt auch. Jetzt versuche ich aus einer Tabelle 2 die Kundennummern zu suchen. Die Tabelle ist wie folgt aufgebaut.
Spalte A stehen die Anbieter Bsp. Telekom, Eon ( erste Zeile frei weil daneben Adressen anfangen.) usw. in Zeile 1 habe ich die Andressen Bsp. Straße xyz, Straße 1 xyz usw. die Tabelle ist von A1:AJ43 aufgebaut und darin befinden sich die Kundennummern. Jetzt versuche ich in der Suche das so einzugeben das nach 2 Kriterien gesucht werden soll Also Anbieter und Adresse der Schnittpunkt soll dann die Kundennummer Ausspucken. Können Sie helfen?
Ich habe zwar nicht auf einer extra Tabelle. Aber mal schauen. Immerhin klappt es.
Hallo Martin,
eine sehr interessante Hilfe. Allerdings habe ich ein Anliegen. Ich arbeite mit der INDEX & VERGLEICH Formel. Allerdings sind meine Rohdaten immer unterschiedlich. z.B. Jan.20 KK 0_Z. oder im Folgemonat Feb.20 KK 0_Z Ich benötige eine Erweiterung der Formel, damit ich z. B. nach KK suchen kann, da dieses Aussage in meiner Auswertungsdatei steht. Leider muss ich die Rohdaten jeden Monat neu erstellen auch die Spaltenüberschriften.
Ich hoffe, dass war verständlich.
Viele Grüße
Sandy
Hallo Sandy,
letztendlich spielt es ja keine Rolle, wie die Spalten heißen. Du kannst mit der Formel genauso gut nach „Jan.20 KK 0_Z“ suchen. Oder stehe ich gerade irgendwo auf dem Schlauch?
Schöne Grüße,
Martin
Hallo Martin,
da hast Du recht.
Sorry falsch ausgedrückt. Wir machen eine monatliche Auswertung. Ich möchte aber die Formeln nicht jeden Monat anpassen, deshalb war meine Idee nur nach KK z.B. zu suchen ohne Monat ohne Jahr. Dann hätte ich eine Formel für alle 12 Monate. Die Daten werden in unterschiedlichen Tabellenblättern ausgewiesen, sodass wir am Ende des Jahres einen Überblick haben.
Hallo Sandy,
verstehe. Eine ungefähre Suche ist hier jedoch leider nicht möglich.
Schöne Grüße,
Martin
Hallo Herr Weiß,
seitdem ich INDEX in Ihrem Blog kennen gelernt habe, bin ich ein absoluter Fan dieser Excel-Funktion. Vielen Dank dafür.
Jetzt habe ich eine Anwendung, zu der ich weder bei Ihnen noch sonst im Internet bisher eine Lösung finden konnte.
Ich habe eine Tabelle, in die mittels INDEX-Funktion Vergütungswerte eingetragen werden. Diese entstammen einem zweiten Tabellenblatt und sind dort als Matrix abgelegt (y-Achse Vergütungsgruppen, x-Achse Vergütungsstufen). Soweit so gut.
Ich würde aber nicht nur die Vergütungswerte für dieses Jahr suchen lassen wollen, sondern auch für vergangene Jahre. Dazu habe ich mehrere identisch aufgebaute Tabellenblätter mit der Vergütungsmatrix für das jeweilige Jahr anlegt und Excel sollte mittels eines Zellwertes auf dem ersten Tabellenblatt entscheiden, in welchem Tabellenblatt (Jahr) die INDEX-Funktion suchen muss.
Vielleicht ist diese Frage bzw. Lösung ja auch für den/die eine/n oder andere/n Mitleser/in interesseant oder jemand kennt eine Lösung.
Vielen Dank!
Hallo Uwe,
ja, die INDEX-Funktion hat es in sich. In Ihrem Fall müssen Sie sie nur mit der INDIREKT-Funktion kombinieren, um variabel auf unterschiedliche Blätter zugreifen zu können.
Beispiel:
Sie geben den gewünschten Blattnamen in Zelle A3 ein und die Datentabellen liegen auf allen Blättern im Bereich B6:E9, dann könnte eine (sehr vereinfachte) Formel lauten
=INDEX(INDIREKT(A3&“!$B$6:$E$9″);1;1)
Schöne Grüße,
Martin
Hallo Herr Weiß,
vielen Dank für die schnelle Antwort. Leider funktioniert die Formel aber nicht bzw. kann nicht funktionieren, weil das erste Tabellenblatt die Anwendung enthält und die Vergütungstabellen erst in den folgenden Tabellenblättern enthalten sind.
Haben Sie auch dafür eine Lösung? Notfalls müsste ich auf dem ersten Tabellenblatt die entsprechenden Zellen leer lassen und ausblenden, aber vielleicht gibt es ja noch eine elegantere Lösung.
Viele Grüße
Uwe
Hallo Uwe,
ich kann ihnen hier leider keine fertige Lösung präsentieren, aber es sollte grundsätzlich auch funktionieren, wenn die Auswertung und die auszuwertenden Tabellen in unterschiedlichen Blättern liegen. Daher ja auch das Konstrukt über INDIREKT.
Schöne Grüße,
Martin
Danke vielmals. Hier habe ich was gelernt und konnte es auch direkt anwenden. 🙂
„=INDEX(Tabelle2!B3:I11;VERGLEICH(D3;Tabelle2!A3:A11;1);VERGLEICH(C6;Tabelle2!B1:I1;0))
+(D3-INDEX(Tabelle2!A3:A11;VERGLEICH(D3;Tabelle2!A3:A11;1)))*
((INDEX(Tabelle2!B3:I11;VERGLEICH(D3;Tabelle2!A3:A11;1)+1;VERGLEICH(C6;Tabelle2!B1:I1;)))-(INDEX(Tabelle2!B3:I11;VERGLEICH(D3;Tabelle2!A3:A11;1);VERGLEICH(C6;Tabelle2!B1:I1;))))/
((INDEX(Tabelle2!A3:A11;VERGLEICH(D3;Tabelle2!A3:A11;1)+1))-(INDEX(Tabelle2!A3:A11;VERGLEICH(D3;Tabelle2!A3:A11;1))))“
Hallo panzerbeere,
das hört man gerne. Und was für ein schönes „Formelmonster“ dabei herausgekommen ist, ich bin begeistert 🙂
Schöne Grüße,
Martin
Hallo Martin,
ich muss jetzt doch mal ein Kommentar hinterlassen.
Immer sehr gut durchgearbeitete Beiträge. Leicht verständlich, wahrscheinlich auch für Excel-Angsthasen und immer auf den Punkt gebracht.
Vielen Dank für die tolle Arbeit.
Beste Grüße Martin 🙂
Hallo Martin,
vielen Dank für das tolle Feedback, das freut mich sehr 🙂
Schöne Grüße,
Martin
Hallo Martin,
Danke, das war meine Rettung. Ich hatte mir mit VERWEIS und SVERWEIS so richtig die Karten gelegt … und die Chefin wurde schon langsam ungeduldig. Deine Empfehlung konnte ich anwenden. Gute Beschreibung – kurz und knackig und trotzdem auch für mich als „älteren Standardanwender“ und ebenfalls Excel-Fan sehr informativ. Nochmals Danke. Wenn ich mehr Zeit hätte, würde ich ja gerne noch weitere Deiner Artikel lesen. Mal sehen, wie das gelingt. Die Links habe ich mir kopiert.
Grüße aus Berlin-Biesdorf – Udo
Hallo Udo,
vielen Dank für dein Feedback und es freut mich sehr, wenn mein Artikel weiterhelfen konnte!
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für den sehr hilfreichen Artikel. Ich habe meine Indexformel inkl. Vergleich inzwischen auf je 2 Suchkriterien für Spalte und Zeile aufgebohrt. Funktioniert super. 🙂
Gibt es noch die Möglichkeit einer Fomel-Erweiterung im Sinne von Summierung von z. B. Spaltenergebnissen, da die Indexformel ja nur den ersten Treffer angezeigt?
Besten Dank vorab.
VG Michael
Hallo Michael,
du meinst, falls z.B. mehrere Zeilen für das gleiche Produkt enthalten sind, dass dann die Summe gebildet wird? Nein, ich denke, das ist mit einem Konstrukt aus INDEX und VERGLEICH nicht möglich. Da müsste man stattdessen vermutlich mit SUMMEWENNS und BEREICH.VERSCHIEBEN arbeiten.
Schöne Grüße,
Martin
…hallo Martin dass ist ganz praktisch und recht hilfreich. Was ich jedoch mich frage ob es mein Problem lösen kann.
Eine Tabelle hat im Normalfall in jeder Zelle einen Wert der aus zwei Kriterien sich kreuzt – also zweidimensional X-Achse und Y-Achse.
Ich habe aber das Bedürfnis die Z-Achse noch hinzuzumachen (quasi eine räumliche Tabelle),aber das kann ich mir ehrlichgesagt bei Excel nur noch mit darüberliegenden Folien wie beim CAD vorstellen. Hast du da eine Lösung – vielleicht mit Indizes wie in Mathe?
Hallo Stefan,
in Excel ließe sich eine dritte Dimension über die verschiedenen Tabellenblätter darstellen. So lassen sich beispielsweise auch mehrdimensionale Summen abbilden – also Summen über mehrere Tabellenblätter. Ob und wie mit INDEX und VERGLEICH auch noch mehrere Blätter einbezogen werden können, hängt vom konkreten Aufbau der Tabellen und dem Anwendungsfall an. Und das ist sicherlich eine relativ komplexe Angelegenheit, die sich hier auch nicht mit ein, zwei Sätzen beschreiben lässt.
Schöne Grüße,
Martin
Hallo Martin,
Vielen Dank für die Ausführungen zu Index und Vergleich, sie sind sehr gut verständlich. Ich habe Allerdings folgende Schwierigkeit: Die Tabelle hat mehrfach beide Suchkriterien, sie soll aber nur dann funktionieren wenn in der Ausgabespalte eine 1 steht und dann nur aus der Zeile den Wert ziehen. Gibt es hier eine schlanke Lösung?
Hallo Christina,
freut mich, wenn der Artikel gefallen hat. Wenn ich dich richtig verstehe, kommt also ein weiteres Suchkriterium dazu. Aus der Ferne ist das natürlich etwas schwierig, aber vom Grundsatz her müsstest du vermutlich die jeweilige VERGLEICH-Funktion innerhalb von INDEX um das zusätzliche Kriterium erweitern. So wie in meinem Beispiel in der zweiten VERGLEICH-Funktion schon mehrere Kriterien mit einem & verknüpft sind, müsste dann noch ein weiteres Kriterium aufnehmen.
Wie gesagt, schwer aus der Distanz zu beurteilen, aber vielleicht hilft das schon ein wenig weiter.
Schöne Grüße,
Martin
Guten Tag Herr Weiss
Ich suche eine Formel für einen Vergleich und nachträgliche Zuordnung in eine Gruppe.
Mein Problem:
Wir haben Masse aus einem Devi und müssen die dann vergleichen mit der Realität (Massaufnahme von der Baustelle).
Devi:
Breit Höhe
120mm x 120mm
160mmx 220mm
180mmx 50mm
Massaufnahme vom Objekt
110x 110mm
150x 160mm
190x 55mm
Wir haben je nach Devi Grösse 20 unterschiedliche Masse mit x Stücken.
Am Objekt haben wir ca. 100 unterschiedliche Masse, die ich gerne mit einer Formel dem Devi zuordnen möchte ohne gross Zeit zu vergeuden.
Hätten Sie da eventuell eine Idee wie ich das Lösen Könnte?
Freundliche Grüsse
Andreas Thöni
Hallo Herr Thöni,
im Moment bin ich noch etwas ratlos, denn mir ist nicht klar, nach welchen genauen Regeln eine Zuordnung von Objektmassen zu Devi-Massen erfolgen soll (was auch immer ein Devi sein mag 😉 ).
Können Sie vielleicht etwas genauer erklären, wie diese Zuordnung aussehen soll?
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die gute Erklärung zur Kombination aus INDEX und VERGLEICH.
Leider schaffe ich es nicht mein Problem damit zu lösen, wahrscheinlich, weil VERGLEICH nicht in einer Matrix suchen kann.
Ich möchte auf einem Tabellenblatt 2 (in Abhängigkeit von dem Wert in Spalte A) in den hinteren Spalten die Werte von einem Tabellenblatt 1 darstellen. Der Wert (Tabellenblatt 2, Spalte A) kann in dem Tabellenblatt 1 in der Matrix AA2:AL51 vorkommen. Wenn der Wert dort erscheint soll die Zeile davor Tabellenblatt 1 (A1 bis Z1) auf dem Tabellenblatt 2 dargestellt werden.
Gibt es in diesem Fall eine Alternative zur VERGLEICH-Funktion?
Vielen Dank und viele Grüße
Oliver
Hallo Oliver,
ja, VERGLEICH kann nur in einer Spalte oder einer Zeile suchen, nicht in einer Matrix. Eine konkrete Alternative für den beschriebenen Fall habe ich jetzt auf Anhieb leider auch nicht parat.
Schöne Grüße,
Martin
Hallo Herr Weiß,
in einer kombinierten INDEX und VERGLEICH-Formel soll mir die VERGLEICH-Formel die Position zurückgeben, wo 2 unterschiedliche Kriterien in 2 unterschiedlichen Zeilen erfüllt sind. Etwas konkreter:
Ich suche nach dem Datum, an dem der Saldo = 0 ist.
Das Wort „Saldo“ kommt dabei in Zeile 1 mehrfach vor und der Wert „0“ kommt in Zeile 2 auch mehrfach vor.
Ich suche nun konkret nach der Stelle, wo beide Kriterien zum ersten Mal erfüllt sind.
Dies ist mein aktueller Stand: INDEX(DE3:AOB3;1;VERGLEICH(0;DE6:AOB6;0))
Jetzt möchte ich zusätzlich noch in dem Bereich DE6:AOB6 nach dem Wort „Saldo“ suchen.
Haben Sie vielleicht eine Idee, wie sich das lösen ließe? Ich hoffe, ich konnte den Sachverhalt in der Kürze verständlich darlegen.
Vielen Dank und viele Grüße
Matthias
Hallo Matthias,
Sie könnten versuchen, die beiden Kriterien zu einem zusammenzufassen. Zum Beispiel so:
=INDEX(DE3:AOB3;1;VERGLEICH(„Saldo0“;DE5:AOB5&DE6:AOB6;0))
In Zeile 5 wird also das Wort „Saldo“ erwartet, in Zeile 6 der Wert 0
Schöne Grüße,
Martin