Eine bei vielen Anwendern oft verschmähte oder wenig verstandene Funktion führt völlig zu Unrecht ein ziemliches Schattendasein in der Excel-Welt: INDEX.
Dabei bietet diese unscheinbare Funktion das Potential, selbst die universelle SVERWEIS-Funktion blass aussehen zu lassen, wenn man ihr noch einen Partner zur Seite stellt.
Das neue Dream-Team heißt daher:
- INDEX
- VERGLEICH
Und so geht’s:
Als Beispiel dient mir heute eine Tabelle mit den DAX-Werten des vergangenen Monats:
Beispieltabelle: DAX-30
INDEX
Die Index-Funktion gibt aus einer Tabelle (Matrix) einen bestimmten Wert zurück, den man über eine Zeilen- und Spaltennummer auswählt:
=INDEX(Matrix; Zeile; Spalte)
Im folgenden Beispiel liefert somit die Funktion den Wert in der 10. Zeile und 3. Spalte meiner DAX-Tabelle:
Einfache Anwendung
So weit noch ziemlich unspektakulär. Seine wahre Stärke spielt die INDEX-Funktion jedoch in Kombination mit der VERGLEICH-Funktion aus. Denn hier kann man die Grenzen von SVERWEIS überwinden.
VERGLEICH
Wie wir oben gesehen haben, liefert INDEX einen bestimmten Wert zurück. Die Funktion VERGLEICH hingegen liefert stattdessen eine Position.
=VERGLEICH(Suchkriterium; Matrix; Vergleichstyp)
Bleiben wir bei meiner DAX-Tabelle. Wenn ich z.B. den höchsten Tageshöchstwert ermittlt habe, dann zeigt mir die VERGLEICH-Funktion, in welcher Zeile sich dieser Wert befindet:
Position bestimmen mit VERGLEICH
Zur Info: Der angegebene Vergleichstyp 0 besagt, dass ich nach einer exakten Übereinstimmung suche.
Damit weiß ich zwar, dass sich der absolute Höchstwert in Zeile 7 befindet. Ich möchte mir aber stattdessen gerne das Datum anzeigen lassen. Da sich die Datumsspalte aber links von meiner Tageshoch-Spalte befindet, hilft mir die SVERWEIS-Funktion nicht weiter. Denn die kann nur Werte ermitteln, die sich rechts von der Suchspalte befinden.
Jetzt schlägt die große Stunde für unser Dream-Team INDEX und VERGLEICH:
INDEX im Team mit VERGLEICH
Was passiert hier?
Wie wir oben gelernt haben, gibt der zweite Parameter in der INDEX-Funktion die Zeilennummer an. Diese gebe ich aber nicht fix an, sondern ermittle sie mit Hilfe der VERGLEICH-Funktion aus dem vorangegangenen Beispiel. Als dritten Parameter für die Spaltennummer gebe ich die 1 an, da sich das Datum ja in der ersten Spalte befindet. Und erhalte damit das gewünschte Ergebnis, nämlich den 02.12.2013.
Wie man sieht, lässt sich mit der Kombination aus VERGLEICH und INDEX im Gegensatz zum SVERWEIS nicht nur nach rechts, sondern auch nach links suchen!
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.
Pingback: Automatische Sortierung (Teil 1) | Der Tabellen-Experte
Aber was mache ich, wenn der Höchstwert in der Tabelle zB zweimal vorkommt (10.01. und 31.08.)?
Hallo Jörg,
das kommt darauf an, welcher der beiden identischen Werte dann angezeigt werden soll. Die VERGLEICH-Funktion setzt ja eine sortierte Liste voraus (im Beispiel nach Datum sortiert). Dementsprechend sollte die Liste aufsteigend sortiert sein, wenn der datumsmäßig erste Wert gefunden werden soll bzw. absteigend, wenn der letzte Wert gewünscht wird.
Grüße,
Martin
Hallo Martin,
schön hast du das erklärt aber wie verhält es sich wenn man zur Bestimmung der korrekten Zeile zwei oder mehr Bedingungen braucht. Wie verknüpft man in der Index-Funktion zwei Vergleich-Funktionen?
Hallo Sebastian,
das geht nur über eine Array-Formel, bei der die zwei (oder mehr) Suchkriterien sowie die zwei (oder mehr) zu durchsuchenden Spalten mit dem Ampersand-Zeichen verknüpft werden. Beispiel:
{=INDEX(A5:A28;VERGLEICH(B2&C2;B5:B28&C5:C28;0))}
In B2 und C2 stehen die beiden Suchkriterien, die Spalten B5:B28 und C5:C28 werden nach diesen Kriterien durchsucht. Und zum Schluß wird der Wert aus Spalte A5:A28 ausgegeben.
WICHTIG: Die geschweiften Klammen dürfen nicht von Hand eingegeben werden. Stattdessen muss die Formel mit STRG+Umschalt+Enter abgeschlossen werden.
Schöne Grüße,
Martin
Hallo Martin,
leider kommt es bei mir auch vor das Daten doppelt vorkommen und das mit den Suchkriterien wie du es erklärt hast, klappt bei mir nicht ganz. Und zwar ist bei mir das Suchkriterium ein Zeitraum, heißt: z.B. >=01.09.2016 und =%Datum%)*(A1:A20<=%Datum%))}
Vergleichs-Wert:
=VERGLEICH(MAX;B1:B20;0)
INDEX:
=Index(A1:A20;VERGLEICH(MAX;B1:B20;0);1)
(Spalte A = Datum; Spalte D = Werte)
Wie kriege ich meinen Zeitraum nun in die Index-Formel gebaut?
Beim MAX-Wert wird mit der richtige Wert aus dem Zeitraum wiedergegeben. Nur beim Index wird wieder die ganze Tabelle, heißt Zeitraum von 01.01.2016 bis 30.09.2016, betrachtet und da kann ein Wert doppelt vorkommen.
Ich hoffe du kannst helfen.
Danke und Grüße
Oliver
Hallo Martin,
einen für mich wichtigen Vorteil der Kombination INDEX und VERGLEICH, kann ich gar nicht hoch genung loben:
Füge ich in eine Tabelle die mit SVERWEIS durchsucht wird neu Spalten (oder bei der Suche in Zeilen neue Zeilen ein), dann darf ich nicht vergessen die Spalten oder die Zeilen – Anzahl anzupassen.
Bei der Kombination INDEX und VERGLEICH bin ich dieser Aufgabe enthoben!
Viele Grüße
Thorsten Geise
-und Danke noch mal für den Hinweis zur Verwendung von SUMMEWENN. Ich habe es abel letztendlich so gelöst, dass ich die Tabelle in eine „intelligente“ Tabelle umgewandelt habe und die Stundenauswertung über eine PIVOTE-Tabelle durchführe. Das hat den Charm, dass ich nun noch andere beliebige Auswertungen schnell ausführen kann.
Danke für Deine Arbeit!.
Hallo Thorsten,
das mit der Anpassung der Spaltennummer ist ein guter Hinweis, so etwas wird tatsächlich häufig vergessen.
Und Pivot-Tabellen sind natürlich generell eine gute Wahl 🙂
Schöne Grüße,
Martin
Du bist geil !
Ich suche nach Tagen nach einer scheinbar nicht auffindbaren lösung für ein ziemlich bescheuertes, teures und nerviges problem
vergleich und index lösen es mit ultra kurzen zeilencode
ich danke dir!
ich liebe Dich ! :-*
Hi Lutz,
gern geschehen. Und wer hätte gedacht, dass Excel mal zu einer solch schönen Liebeserklärung führt 🙂
Schöne Grüße,
Martin
Oh danke, das hat meinen Excel-Horizont erweitert und ein anliegendes Problem elegant gelöst!!
Gern geschehen, freut mich zu hören!
Schöne Grüße,
Martin
super – Martin, guter Tip, komme aber auch nicht weiter, hab ein ganz anderes Problem:
ich stelle mit grad eine Tabelle für meine Standard-Therapie (COPD/Asthma) zusammen, habe eine kleine Verweisliste (6 Medikamente und jeweils die Dosierung dazu – JETZT aber sollte nur bei einem Medikament ein dropdownfeld
zur Dosierung „entstehen“, für die anderen nicht – geht das überhaupt,
falls nicht, wie kriegt man das mit dem Datum in dem Verweisfeld für dieses spezielle Medikament hin (ab 6. Tag 1 Tablette, ab 12 Tag 3/4 Tablette) ausgehend vom Startdatum der Behandlung? Danke Ihnen/Dir im Voraus
Hallo Andreas,
Dropdown-Felder werden ja über die Gültigkeitsüberprüfung eingerichtet. Daher legst Du erst einmal selbst fest, in welchen Zellen du sie brauchst und in welchen nicht. Grundsätzlich lässt sich eine Dynamik einrichten, die abhängig von einem Wert in einem Feld unterschiedliche Inhalte in der Dropdown-Liste bereitstellt.
Zu diesem Thema gibt es einige Artikel hier auf dem Blog, z.B.
https://www.tabellenexperte.de/dynamische-drop-down-listen-teil-1/
https://www.tabellenexperte.de/dynamische-drop-down-listen-teil-2/
Vielleicht wirst du dort fündig.
Schöne Grüße,
Martin
Hallo ihr Fließigen,
ich arbeite gerade auch mit Index und Vergleich. Ich frage ab, ob in der Spalte B mein Wert X steht. Wenn das so ist, dann soll er die Zellwerte rechts daneben addieren. Funktioniert auch wunderbar. Allerdings steht der Wert X öfter in Spalte B und alle Werte, die daneben stehen, sollen addiert werden und nicht nur die vom zuerst gefundenen. Wisst ihr wie ich das lösen kann?
Meine aktuelle Formel: =SUMME(INDEX(A:O;VERGLEICH(R7;B:B;0);))
Wäre toll, wenn ihr hier eine Lösung hättet.
Viele Grüße,
Judith
Hallo Judith,
ich kenne zwar den Aufbau Deiner Tabelle nicht, aber das hört sich für mich eher nach einem Fall für eine SUMMEWENN-Funktion an.
Schöne Grüße,
Martin
Hallo,
ich habe gehofft mein aktuelles Excel-Problem mit einer Index/Vergleich Formel zu lösen, bekomme aber kein Ergebnis. Es sieht wie folg aus: Ich habe 2 Suchkriterien. Spalte A enthält Tarif Bezeichnungen und Spalte B Verbrauchsstufen. D. h. jeder Tarif hat ähnliche zum Teil die gleichen Verbrauchsstufen (Tarif A z.B. ab 0 ab 5.000 ab 50.000; Tarif B ab 0, ab 5.000 ab 150.000). Jeder Tarifs-/Verbrauchstsufenkombination ist ein Preis zu geordnet, welcher ausgegeben werden soll. Ich habe nun als zwei Suchkriterien Tarif-Name (Feld B34) und einen konkreten Verbrauch wie z.B. 27.106 (Feld B33).
Meine gebaute Formel sieht folgendermaßen aus:
{=INDEX(C3:C21;VERGLEICH(A26&B26;A3:A21&B3:B21))}
Ausgegeben bekomme ich allerdings nur den Wert in der obersten Zeile. Mit Vergleichstyp -1 (größer als) kommt #NV.
Ich habe nun schon das halbe Internet durchforstet und komme zu keinem Ergebnis.
Evtl. kannst Du mir helfen oder hast einen anderen Ansatz zur Problembewältigung für mich.
Vielen Dank im Voraus.
LG Susanne
Hallo Susanne,
eine interessante Aufgabenstellung! Die Kombination der Spalten A und B in der VERGLEICH-Funktion bringt Dich nicht weiter, da der gesuchte exakte Wert in Spalte B nicht vorhanden ist (zumindest wenn ich es richtig verstanden habe).
Ich würde es mit einem SVERWEIS probieren, der als Matrix einen dynamischen Bereich über die BEREICH.VERSCHIEBEN-Funktion verwendet. Das könnte so aussehen:
Die Formel in B35 lautet:
=SVERWEIS(B33;BEREICH.VERSCHIEBEN(A1;VERGLEICH(B34;A1:A7;0)-1;1;ZÄHLENWENN(A1:A7;B34);2);2;WAHR)
Mit der VERGLEICH-Funktion wird die Startzeile für den Tarif gesucht.
ZÄHLENWENN berechnet die Anzahl der Zeilen innerhalb des Tarifs.
Voraussetzung ist lediglich, dass die Tabelle nach Tarif und Verbrauchsstufen sortiert ist.
Schöne Grüße,
Martin
moin Herr Weiß ,
auf meiner unendlicher Suche bin ich jetzt auf Ihre Seite Gestoßen und möchte Sie höflichst um Hilfe bitten.
Mein Problem :
Ich speichere mir tägl. Den akt. Konto Auszug in eine Tabelle und ziehe mir die jeweiligen Daten die ich benötige .
solange wie an einem Tag nur eine Transaktion gelaufen ist passt das auch alles . aber sobald es noch eine oder sogar noch mehrere gab wird es schwierig .kann Excel erkennen das er den ersten wert zu dem Datum schon wiedergegeben hat und mir automatisch den nächsten gegeben ?
wäre echt froh von Dir zu hören
Mfg Mirko
Hallo Mirko,
ich bleibe jetzt auch einfach mal beim „Du“ 🙂
Das von dir beschriebene Szenario ist nicht ganz einfach zu lösen. Die normalen Excel-Funktionen (SVERWEIS, INDEX etc.) greifen immer auf den ersten Wert zurück. Hier muss man schon ziemlich tief in die Trickkiste greifen, um das zu umgehen. Das ist auch nicht mit ein, zwei Sätzen beschrieben und hängt immer auch vom konkreten Aufbau der Tabellen ab.
Als Tipp kann ich dir daher nur folgenden Artikel nennen:
https://www.tabellenexperte.de/besser-als-sverweis-alle-werte-finden/
Vielleicht hilft das ja ein wenig weiter.
Schöne Grüße,
Martin
Hallo Martin,
danke für diese Ausführliche Erklärung, aber irgendwie löst diese mein Problem nicht.
Ich habe zwei Tabellen mit Artikelnummern die aus Zahlen und Buchstaben bestehen, diese möchte ich Vergleichen und bei Übereinstimmung den Wert einer bestimmten Spalte (in diesem Fall der Preis) ausgeben lassen.
Die Artikelnummern können als Beispiele so aussehen:
A1925E6
1133HC
oder auch einfach nur 1138
Leider habe ich es nicht geschafft dein Beispiel auf auf meien Tabelle anzuwenden.
Hast du vielelicht eine nTip pfür mich?
VG
Marc
Hallo Marc,
das ist aus der Ferne natürlich schwer zu beurteilen. Ein genereller Stolperstein (auch beim SVERWEIS!) in diesem Zusammenhang sind unterschiedliche Formatierungen. Wenn, wie in deinem Beispiel, die Artikelnummer 1138 in einer Tabelle als Zahl formatiert ist und in der anderen als Text, wird die Formel nicht funktionieren.
Schöne Grüße,
Martin
Guten Morgen,
die Formatierung ist in allen Zellen gleich, ich denke mal das es wirklich an der Kombination aus Zahlen und Buchstaben liegt.
Bis jetzt bin ich auf noch keine Lösung gestoßen.
VG
Marc
Hallo, Martin!
Toller Artikel. Wie kann ich zusätzlich zu einer kombinierten Index/Vergleich-Formel noch einbauen, dass er mir den ausgebenen Wert (bei mir „m“ bzw. „w“) in „männlich“ bzw. „weiblich“ umwandelt?
Mein Tabellenblatt zeigt mir verschiedene Personaldaten per DropDown-Auswahl (Feld C2) an. Formel:
=INDEX(Personal!A2:K110;VERGLEICH($C$2;Namen;0);4)
Hallo Peter,
dankeschön. Du könntest die Formel einfach um eine WENN-Funktion erweitern:
=WENN(INDEX(Personal!A2:K110;VERGLEICH($C$2;Namen;0);4)=“m“;“männlich“;“weiblich“)
Schöne Grüße,
Martin
Hallo Martin,
besten dank für deine Seite. Deine Beiträge haben wir schon ein übers andere Mail geholfen. Ich habe jetzt jedoch eine komplexere Problemstellung.
Wir haben einen Jahresurlaubskalender, wo in Spalte A der Name des Mitarbeiters steht und in Zeile 2 das Tagesdatum. Hat ein Mitarbeiter Urlaub an einem Tag ist entsprechend in seiner Spalte unterhalb des Datums ein „u“ eingetragen. ich würde jetzt gerne alle Tagesdaten, wo ein „u“ eingetragen wurde je Mitarbeiter ausgeben.
Beispiel: Mitarbeiter Schmitz hat am 2., 3. und 4.Juni Urlaub, also auch in den beiden Feldern ein „u“ eingetragen. Wie muss ich die Formel aufbauen, damit ich zum Mitarbeiter die beiden Daten ausgegeben bekomme, sprich mir der 2.6.19, 3.6.19 und 4.6.19 ausgegeben wird. Das Sahnehäubchen wäre jetzt noch, wenn mir besagte Urlaubstage als Zeitraum, also 2.-4.6. angezeigt werden würden. Hast du eine Idee?
Beste Grüße
Hallo Sebastian,
so etwas lässt sich lösen, ist aber nicht mit ein, zwei Sätzen hier zu erklären. Aber ich werde genau dazu in einem der nächsten Blog-Artikel etwas schreiben. Daher muss ich dich noch um etwas Geduld bitten 🙂
Schöne Grüße,
Martin
Vielen Dank, ist genial. Leider habe ich ein kleines Problem:
Ich habe eine Überschrift, in unterer Formel ist das B2:O2
=INDEX($B$2:$O$2;VERGLEICH(MIN(C4:C12&E4:E12);C4:C12&E4:E12;0))
Aus den unteren Suchkriterien, soll es mir den tiefsten Betrag wiedergeben und dann die Überschrift, auf den es sich bezieht, wiedergeben.
Bei einem Testlauf hat es geklappt, da das Suchkriterium sich auf die ganze Tabelle bezogen hat.
Bei diesem sollen nur gewisse Zellen gesucht werden und irgendwie pack ich die Formel nicht. Auch mit den geschweiften Klammern passiert überhaupt nichts. Excel scheint zu denken, weil die geschweifte Klammer vor dem Gleichheitszeichen ist, dass es keine Formel ist. Und mit Strg (bei mir Ctrl) +Shift+Enter passiert nichts
Vllt. habt ihr eine Idee?
Vllt. sollte ich noch erwähnen, dass bei mit #NV angezgit wird, wenn ich die geschweiften Klammern eingeben möchte.
Hallo Coco,
kannst du vielleicht noch ein paar Beispiele geben, was in den Spalte C und E konkret drinsteht. Im Augenblick werde ich noch nicht so ganz schlau aus der Beschreibung…
Schöne Grüße,
Martin
Danke für deine Antwort, Martin. Hoffe untenstehende Infos reichen.
Die Spalten sehen so aus:
In Spalte A stehen Daten wie z.B. 01.01.2019 – 07.01.2019 (die Daten interessieren nicht)
In den nachfolgenden Spalten stehen Beträge:
Spalte B in der 2. Zeile – Überschriften sind z.B. Firmennamen
Die Beträge stehen in ab der 4. Zeile:
1200
Spalte C in der 2. Zeile
1100
Spalte D in der 2. Zeile
1500
Spalte E in der 2. Zeile
1800
Der Grund warum ich die Spalten selektieren muss ist, weil ich nur das Minimum alle zwei Spalten brauche. Leider müssen die Spalten in der Reihenfolge bleiben, sonst wäre es leichter einfach die ganze Daten zu markieren. Das hat nämlich ohne Probleme geklappt.
Danke und Gruss
Hallo Coco,
tut mir sehr leid, aber jetzt bin ich noch verwirrter als zuvor. Vielleicht steht ja ein anderer Leser weniger auf der Leitung…
Schöne Grüße,
Martin
Moin moin,
ich komme mit der Kombination aus den beiden Funktionen leider nicht so recht weiter.
Mein Vorhaben ist:
Aus einer Zeile Bsp.: A1 bis A14 einen Wert zu suchen, wenn dieser Wert gefunden wurde, dann soll an anderer Stelle der Wert aus der Zelle links daneben ausgegeben werden. Das funktioniert aber leider nicht. Kombiniere ich Vergleich mit Index kommt immer ein Fehler. 🙁
Besten Gruß
Joschka
Hallo Joschka,
das ist grundsätzlich genau das, was oben im Artikel beschrieben ist. Allerdings irritiert mich dein Beispiel ein wenig: Links von A1 bis A14 kann nichts mehr stehen…
Aber vielleicht habe ich dich auch missverstanden.
Schöne Grüße,
Martin
Ich bin Neuling und benötige Hilfe. Kann man bei einer Kombination von Index und Vergleich in der Matrix die Überschriften von Zeile und Spalte gleich benennen? Beisp.: Spaltenüberschrift A2 bis A5: a,b,c,d, Zeilenüberschrift A2 bis A5 auch a,b,c,d. jeder Schnittpunkt von Zeile und Spalte ist mit einem unterschiedlichem Wert versehen. Zeilenüberschrift G1=a; Spaltenüberschrift G2=b;
Formel: =index(A1:E5;vergleich(G1;A2:A5;0;vergleich(G2;A2:A5;0) das Ergebnis ist:“Sie haben zu viele Argumente für diese Funktion eingegeben“. Worin liegt mein Fehler? Hat das mit den gleichlautenden Zeilen- und Spaltenüberschriften zu tun? Oder sehe ich „den Wald vo lauter Bäumen nicht“.
Mit freundlichen Grüßen
Jürgen
Hallo Jürgen,
ich komme leider mit deinen Zellenangaben nicht klar: Du schreibst „Spaltenüberschrift A2 bis A5“ und gleichzeitig „Zeilenüberschrift A2 bis A5“. Ich nehme mal an, die Spaltenüberschrift steht eher in B1 bis E1?
So oder so fehlt zumindest eine Klammer in deiner Formel, bevor die zweite VERGLEICH-Funktion beginnt und zusätzlich am Ende der Formel. Also so:
=index(A1:E5;vergleich(G1;A2:A5;0);vergleich(G2;A2:A5;0))
Aber wenn die Tabelle so aussieht, wie ich vermute, müsste die Formel so lauten:
=INDEX(A1:E5;VERGLEICH(G1;A1:A5;0);VERGLEICH(G2;A1:E1;0))
Schöne Grüße,
Martin
Ich bin verwundert, dass so was als Dream-Team hochstilisiert wird. Es ist schon eine Schande, dass Excel nicht in der Lage ist, so etwas triviales in einer Funktion zu gestalten. Das entspricht aber dem inkompetenten Verhalten von Microsoft und deren eindimensionalen Gedankengängen. Eine Matrixfunktion, welche mir den Wert aus einer Zielposition mit Rückgabe des Wertes um x Spalten und y Zeilen zurückgibt währe Hilfreich und ein echter Allrounder, denn mit so einer Funktion lassen sich praktisch alle Matrixabfragen bewerkstelligen. Doch dazu wird Excel wohl nie in der Lage sein.
mit VBA kannst aber selbst Funktionen schreiben, die das vielleicht lösen.
Vielleicht bekommst du ja hin, wo Microsoft scheitert.
Hallo Martin,
kann ich mit dem „Dream-Team INDEX und VERGLEICH“ und der Bedingten Formatierung die identifizierte Zelle hervorheben, oder gibt es dort einen anderen Weg?
Hallo McB,
wenn es wie im Beispiel oben um die Zelle mit dem Maximalwert geht, kannst du eine der fest eingebauten Formatierungsregeln nutzen:
Start | Bedingte Formatierung | Regeln für oberste/unterste Werte | Obere 10 Elemente…
Dann änderst du den Wert 10 auf 1 und bekommst die Zelle mit dem Maximalwert hervorgehoben
Schöne Grüße,
Martin
Hallo Marin,
ich habe schon mehrere Formeln versucht auch versucht anzupassen, aber leider ohne den gewünschten Erfolg.
Hier ist mein Dilemma…
Ich in einer Tabelle auf einem anderen Tabellenblatt Firmen (O:O), Crews (P:P), Startdatum (E) und Enddatum (F) sowie die jeweilige Baustelle (B) zu stehen.
Jetzt möchte ich gern die Auslastung darstellen. Also wie lange ist die Crew X auf der Baustelle.
Ich kann über dropdown die jeweilige Firma auswählen ($B$2) und dann erscheinen von der Firma alle Crews (A$6:A$15)
in der Reihe 5 (B5:U5) zeige ich den jeweiligen Freitag der Woche an.
Jetzt möchte ich gern sehen wann ist die Crew an welcher Baustelle wie lange beschäftigt.
Ich habe eine Formel gefunden die mir etwas liefert, aber nur nonsens…
=INDEX(REPORT!$B:$B;SUMMENPRODUKT((REPORT!$E$5:$E$188 =D$5)*(REPORT!$O$5:$O$188 =$B$2)*(REPORT!$P$5:$P$188 =$A6)*ZEILE(REPORT!5:188)))
oder: =WENN(F15″“;WENNFEHLER(INDEX(REPORT!$B:$B;VERGLEICH(1;MMULT((REPORT!$P$5:$P$188=$A15)*(REPORT!$E$5:$E$188 =G$5);1);0));““);““)
Falscher Lösungsansatz oder Denkfehler…
Hilfe…
Vielen Dank.
Grüße
Hinnerk
Hallo Hinnerk,
das ist leider schwer zu sagen, da müsste man jetzt die Datei nachbauen, um die Formeln besser nachvollziehen zu können.
Hast du es alternativ schon mal mit einer Pivot-Tabelle versucht?
Schöne Grüße,
Martin
Hallo Martin,
es sieht so einfach aus. Auch denke ich, das diese Funktion bei mir funktioniert hat. Leider stellte ich jetzt fest, das beim Ausführen dieser und wenigstens einer weiteren Funktion ein #Bezug-Fehler auftritt, den ich mir nicht erklären kann. Nach Aufruf von Excel und erzeugen einer neuen Mappe, tritt auch hier der #Bezug-Fehler auf. Daher habe ich die Vermutung, dass in Ms-Excel irgendeine Einstellung geändert wurde, die diesen Fehler verursacht. Vielleicht habe ich die Funktion nicht richtig verstanden, da ja zwei „Index“-Einstellungen möglich sind.
Ich hänge einen Link an mit dem Ausschnitt der Excel-Mappe. Ganz oben wird die Syntax angezeigt, drunter die Liste und das Ergebnis.
Vielleicht gibt es eine Lösung. Vielen Dank im Voraus.
https://1drv.ms/u/s!AhE29MZB2_wTqXkPtMeYJ6qWdi05?e=gzhOMR
Hallo Harald,
der #Bezug!-Fehler kommt nicht durch eine geänderte Einstellung in Excel. Du versuchst im Beispiel in deinem Screenshot nur auf eine Spalte zuzugreifen, die es nicht gibt.
=INDEX(F2:G13;6;7)
Der zweite Parameter (hier: 6) gibt die Zeile an, der dritte Parameter (hier: 7) die Spalte. Dein Tabellenbereich hat aber nur zwei Spalten (F:G). Die Spaltennummer in der INDEX-Funktion bezieht sich nicht auf die absolute Spalte im Tabellenblatt (dann wäre G tatsächlich Spalte 7), sondern auf die Spaltennummer innerhalb de angegebenen Bereichs. In deinem Beispiel also 2.
Schöne Grüße,
Martin
Hallo Martin,
vorab: toller Beitrag, klasse Webseite! Ich stehe auch vor einem Problem das mich an den Rand der Verzweiflung treibt.
Ich möchte eine Matrix durchsuchen und dabei 5 Bedingungen abfragen. 4 der 5 Bedingungen bestimmen die auszuwählende Zeile, die 5 und letzte Bedingung gibt abschließend die auszuwählende Spalte vor. Leider habe ich nicht die Möglichkeit eine Beispieldatei zur Verfügung zu stellen ich versuche das Problem daher textuell darzustellen:
Tabelle:
| Krit5 | Krit6 | Krit7 |
Krit1 | Krit2 | Krit3 | Krit4 |_____|_____|____|
neu | Auto | AA | DE | 25 | 2 | …
neu | Auto | AA | IT | 3 | … |
alt | Moto | BB | AU | 2 | … |
Ziel der Abfrage soll die Ermittlung der im Beispiel dargestellten Zahlen (z.B. 25, 3, 2) sein um mit dieser Zahl weiter rechnen zu können.
Die Kriterien werden in einem anderen Tabellenblatt über ein Drop-Down-Feld ausgewählt. Für den Fall das die Kriterien 1-4 zutreffen, gibt es nur noch eine Spalte (z.B. Krit5) das die Spalte festlegt. Wie du siehst, sehr kompliziert,…
Ich habe bereits diverse Foren und Webseiten durchsucht – leider gab es nie ein vergleichbares Szenario… Vllt. ist es auch schlichtweg ein in Excel nicht darstellbares Szenario – keine Ahnung… Allerdings glaube ich, dass wenn dieses Problem lösbar ist – dann von einem Experten mit deinem Know-How…
Über eine Einschätzung deinerseits wäre ich Dir sehr dankbar!
Beste Grüße
Flo
Hallo Flo,
danke erstmal für das schöne Feedback. Das Problem ist auf jeden Fall mit Excel lösbar und es gibt sicherlich verschiedene Wege dafür. Eine relativ banale Variante könnte so aussehen:
Du legst in deiner Tabelle eine weitere Spalte an, in der über eine Textverkettung die ersten 4 Kriterien zusammengefasst sind. Also in der Art „neuAutoAADE“ (es spielt keine Rolle, ob diese Spalte vorne oder hinten in der Tabelle steht).
Das gleiche machst du in dem Tabellenblatt, in dem du die Kriterien auswählst. Somit hast du schon mal ein eindeutiges Merkmal für die Zeilenauswahl.
Und jetzt kombinierst du die INDEX-Funktion mit zwei VERGLEICH-Funktionen, um die richtige Zeile und Spalte zu finden.
Vielleicht hilft das weiter.
Schöne Grüße,
Martin
Hallo Martin,
da mir die Formel vor längerem jemand ans Herz gelegt hat, benutze ich sie auch bereits eine Weile. Jetzt kam allerdings etwas neues auf, dass ich versuchen wollte.
Im Normalfall habe ich die Formel mit Spalten genutzt, d.h. Vergleich sucht die Zeile in einer bestimmten Spalte und Index gibt den Wert aus entsprechender Spalte zurück. Jetzt habe ich versucht, dass der Vergleich in der ersten Zeile nach einem Begriff sucht und Index sollte das Pendant in der zweiten Zeile zurückgeben. Der Vergleich funktioniert und wirft Spalte 40 aus, der Index in Zeile 2 hingegen #Bezug. Ich hätte angenommen es würde den Text zurückgeben oder funktioniert es nur mit Zahlen?
Viele Grüße
Matthias
Hallo Martin,
nach stundenlanger erfolgloser Internetrecherche und frustrierenden Versuchen in Excel frage ich hier einmal nach. Ich möchte gern innerhalb einer Zeile (umfasst 10 Zellen), die Position mir ausgeben lassen, an der erstmalig ein Zellenwert größer gleich 2 ist. So habe ich es versucht: =VERGLEICH(„>=2“;JG6:JP6). Leider kommt der Fehler #NV.
Hast du einen Tipp, wie ich dieses Problem lösen könnte?
Liebe Grüße,
Romy
Hallo Romy,
probier’s mal hiermit:
=VERGLEICH(2;JG6:JP6;)
Schöne Grüße,
Martin
Hallo,
jepp…das ist ein echtes Dreamteam – eeendlich habe ich das gefunden, was ich gesucht habe!! 🙂
Vielen Dank!!!
Rob
Hallo Rob,
freut mich zu hören, gern geschehen!
Schöne Grüße,
Martin
Hallo Martin,
ich bin absoluter Fan deiner Seite.
In unserer Firma bin ich IDV-Beauftragte und habe einen Datenservice.
Für deine tollen Tipps danke ich dir sehr herzlich.
Excel finde ich genial und ich glaube es braucht immer ein gutes Gefühl, die richtige Komplexizität zu erkennen, wann Access lieber zum Einsatz kommen soll.
Ich finde, dass man in Excel extrem kreativ sein kann.
Und genau das macht einerseits den Reiz aus, andererseits führen so viele Wege nach Rom und ich sehe Lösungen, bei denen ich denke, aaah so wär’s auch gegangen oder ah so wär’s auch gegangen, aber meins finde ich versionskompatibler udn dann kommt noch die Rechenleistung der Anwendung, je nach Datenvolumen-Soll…
Ich inventarisiere und entwickle IDV-Anwendungen, die Freigabeprozeduren durchlaufen. Anders als bei Softwareentwicklung, bin ich mit der Erstellung solcher Vorlagen immer im Nachteil, wenn Versionen nicht abwärtskompatibel sind, bspw. bei Pivottabellen. Daher weiß ich gar nicht, ob es Sinn macht, Auswertungen per Pivot bereitzustellen und die Gesamtsumme bringe ich irgendwie heute auch nicht nach oben…
Herzliche Grüße mit einem Riesendankeschön für deine tolle Seite.
Conny
Hallo Conny,
vielen Dank für dieses tolle Feedback! Ja, in Excel geht wirklich sehr viel, aber es gibt halt auch den Punkt, wo man besser auf eine Datenbank zurückgreifen sollte – oder auch nur auf eine Textverarbeitung 🙂
Die Schwierigkeit ist halt für viele Anwender immer, diesen Punkt zu erkennen. Und mit Excel können einfach mehr Leute umgehen als mit Datenbanken. Aber man sollte Excel auch nicht für alles missbrauchen.
Schöne Grüße,
Martin
Hallo,
vielen Dank für die ausführliche Beschreibung. Wenn ich den Index Vergleich „andersherum“ machen möchte, d.h. ich möchte, dass eine bestimmte Spalte gesucht wird und dann der entsprechende Zeilenwert (fixe Zeile, variable Spalte) gefunden wird, wie wäre dann die Formel?
Hallo Aurica,
du musst nur in der VERGLEICH-Funktion anstatt einer Spalte eine Zeile durchsuchen lassen und dann die Funktion als letzten Parameter der INDEX-Funktion verwenden. Beispiel:
Du suchst nach dem Buchstaben „c“ in der 3. Zeile der Tabelle und dann soll der Wert aus Zeile 2 ausgegeben werden:
=INDEX(Tabelle1;2;VERGLEICH(„C“;B3:F3;0))
Schöne Grüße,
Martin
Guten Morgen Martin,
leider habe auch ich ein Problem, dass ich bisher nicht lösen konnte.
Ich habe es mit Index/Vergleich probiert, aber es kann in meinem Fall nicht helfen weil:
Eine Spalte exakte Übereinstimmung geben muss, in Spalte zwei den größten Wert davon.
Soll heißen:
Spalte A fortl. Datum;Spalte B Mitarbeiter; Spalte C Verkaufswert
Nun möchte ich wissen, ab einem Datum X, welcher höchster Verkaufswert erzielt worden ist.
Über Pivot geht es nicht, denn ich benötige diese Formel in 15 Zeilen untereinander.
Ich bräuchte eine Formel wie Index/Vergleich, die einen Wert exakt verwendet und den zweiten größer/gleich sucht.
Hast du eine Idee für mich?
Vielen Dank im Voraus
ömö
Hallo Ömö,
für mich hört sich das eher nach einer Kombination aus MAX + WENN-Funktion als Matrixvariante an. Oder, falls Du Excel 2019 oder Office 365 verwendets, einfacher nach MAXWENNS.
Die Matrixvariante (als mit Strg+Umschalt+Eingabe abschließen) wäre etwa so:
{=MAX(WENN(A10:A100>=E10;C10:C100))}
In E10 steht das gewünschte Datum.
Schöne Grüße,
Martin
Hallo Martin,
vielen dank für deinen Tipp.
„Maxwenns „geht leider nicht, denn dann müssten die Werte ja immer identisch sein (also gleich ensprechend).
Ich dagegen benötige ja eine Übereinstimmung und der andere Wert muss gleich oder größer sein.
Ich habe also zwei Variablen, die einmal übereinstimmen müssen und die andere größer/gleich sein muss. Nur dann darf der Wert aus Spalte C ermittelt werden.
Auch die Matrixvariante funktioniert bei mir leider nicht.
Hier wird zwar der max. Wert ermittelt, wenn das Datum größer oder gleich ist, aber der Mitarbeiter aus Spalte B wird hier nicht beachtet.
Ich habe auch versucht
=MAX(WENN(UND(A1>=D1;B1=E1);C1))
Aber es kommt nichts.
Hast du noch einen Tipp für mich?
Danke und VG
ömö
Hallo ömö,
hilft dir die Formel weiter?
=MAX(WENN(B1:B100&“1″=M1&WENN(A1:A100>=D1;“1″);C1:C100))
A1:A100 = Datum
B1:B100 = Mitarbeiter
C1:C100 = Verkaufswert
M1 = Gesuchter Mitarbeiter
D1 = Ab Datum
Gruß Marcel
Hallo Marcel,
deine Idee funktioniert, sobald die Formel als Matrix { } verwendet wird.
Super.
{=MAX(WENN(B1:B100&1=M1&WENN(A1:A100>=D1;1);C1:C100))}
Herzlichen Dank dafür.
Ich wünsche dir frohe und besinnliche Weihnachten.
ömö
Hallo
Folgendes Problem.
=INDEX(A12:A16;VERGLEICH(„tn“;B12:B16;0);1)
In dieser Formel habe ich ein Suchkriterium „tn“ innerhalb der Spalte B12 bis B16. Ich möchte nun noch 2 weitere Suchkriterien einfügen „t“ und „hn“, welche sich im gleichen Spaltenbereich B12:B16 befinden. Im Spaltenbereich kommt immer nur eines der genannten Suchkriterien vor. Dort wo eines der Suchkriterien dann vorkommt zeigt mir Index die Zelle zw. A12:A16.
Wie lautet die Formel. Kann mir bitte jamend helfen
lg THOMAS
Hallo Thomas,
eine mögliche Variante wäre das hier:
=INDEX(A12:A16;WENNFEHLER(VERGLEICH(„t“;B12:B16;0);WENNFEHLER(VERGLEICH(„tn“;B12:B16;0);VERGLEICH(„hn“;B12:B16;0)));1)
Schöne Grüße,
Martin
Diese Tipps sind sehr hilfreich um Lösungsansätze für eigene komplexe Aufgaben zu finden. Vor allem wie ich die verschiedenen Funktionen kombinieren kann. Danke für das kostenlose Teilen der vielen Tipps.
Hallo Celestina,
vielen Dank für das nette Feedback, freut mich sehr, wenn die Artikel weiterhelfen.
Schöne Grüße,
Martin
Hallo liebe Profis,
ich hoffe es kann mir jemand meine Frage beantworten:
Ich habe eine Tabellebereich, angenommen Spalte A bis E, Zeilen 1-10.
Angenommen in Spalte C, Zeile 5 steht ein Wort „Prämie“ und darüber in Spalte C, Zeile 4 eine Zahl z.b „0,5“.
Ich möchte nun, dass in Spalte F der Wert der Prämie also die 0,5 erscheint. (Die Aufgabe wäre: Finde in der Tabelle das Wort „Prämie“ und zeige mir den Wert aus der Zelle eins drüber an.)
(In Spalte C stehen auch noch andere Wörter und Werte, ich möchte aber nur den Wert der Zelle oberhalb der Prämie anzeigen lassen)
Wie würde die konkrete Formel lauten?
Ich rätsle über SVERWEIS, WVERWEIS; INDEX und VERGLEICH, bekomme es aber einfach nicht hin.
Sofern es eine Lösung gibt, würde das auch funktionieren, wenn das zu suchende Wort und der zu suchende Wert in einer Zusammengefügten Spalte ( Spalte C und D) steht?
Vielen Dank im Voraus
LG
Andrea
Hallo Andrea,
bezogen auf den Tabellenbereich in deinem Beispiel wäre die Formel folgende:
=INDEX(C1:C10;VERGLEICH(„Prämie“;C1:C10;0)-1)
Die VERGLEICH-Funktion sucht die Position von „Prämie“ im angegebenen Bereich, bei C5 also die Zeile 5
Die INDEX-Funktion gibt dann den Wert aus der Zeile 5-1 = 4 zurück.
Die Formel dürfte auch bei verbundenen Spalten funktionieren, allerdings rate ich davon eher ab, denn meistens führen verbundene Zellen früher oder später doch zu Problemen bei Berechnungen.
Schöne Grüße,
Martin
Hallo Martin,
vielen Dank für die schnelle Hilfe.
Es passt noch nicht ganz, aber bin schon einen großen Schritt weiter.
Habe den Suchbreich jetzt auf die Zelle und die darüber eingeschränkt und es so in alle anderen Zeilen der Spalte kopiert, habe es noch um ein =WENNFEHLER(INDEX(C4:C5;VERGLEICH(„Prämie“;C4:C5;0)-1);““) ergänzt.
Somit zeigt es kein #NV mehr an und ich kann em Ende eine Summe der Spalte bilden. Juhuu. Ziel erreicht.
An anderer Stelle funktioniert es so nicht, weiß nicht ob es daran liegt, dass dort in der Zelle über dem gesuchten Wort nicht ein Wert, sondern das Ergebnis einer Formel in der Zelle steht? Werde da noch ein bisschen rumspielen. Ist sicher nur ne Kleinigkeit.
Dir sei schonmal die goldene Excel-Ehrennadel verliehen. Unfassbar wie man sowas verstehen kann….
Grüße
Andrea
die hier geschilderte Vorgehensweise findet einen Wert nur in einer einzigen Spalte, richtig?
Gibt es denn auch eine Möglichkeit, einen Wert in einer bestimmten Spalte in einer Tabelle zu suchen und die Spalte zurückzugeben?
In einer formatierten / intelligenten Tabelle habe ich eine bestimmte Anzahl Spalten, in denen untereinander verschiedenen Personen aufgelistet sind. Nun möchte ich den Index der Spalte ermitteln, in welcher ein bestimmter Name steht.
Danke im voraus.
Hallo Tino,
das Problem der Suche über mehrere Spalten ist etwas kniffeliger, die hier vorgestellte Methode ist dafür nicht geeignet. Ich werde aber demnächst einen Artikel genau zu diesem Thema veröffentlichen (Spoiler: Es wird unter anderem die AGGREGAT-Funktion benötigt).
Schöne Grüße,
Martin
Hallo Martin,
wie würdest du es lösen …
Ich habe in Spalte A (2:A62) Datumswerte stehen, dahinter stehen Zahlenwerte.
Nun möchte ich unter der Tabelle Summen für die einzelnen Monate bilden.
Also da steht dann in den Zeilen A70 – A81 „Januar“ bis „Dezember“. Dazu brauche ich das erste Vorkommen von Januar (etc.) in der Spalte B70 – B81 (den Rest kümmere ich mich).
Wie würdest du das lösen?
Tobi@s
Hallo Tobias,
ich würde die Monate (A70 – A81) als echte Datumswerte hinterlegen (z.B. 01.01.2022 für den Januar) und als MMMM formatieren. Danach kannst du mit SUMMEWENNS alle Summen bilden und dabei jeweils Bezug auf die Monatswerte in A70, A71 etc nehmen.
Beispiel für Januar:
A70: 01.01.2022 (formatiert als MMMM, wird somit als „Januar“ angezeigt)
B70: =SUMMEWENNS($B$2:$B$62;$A$2:$A$62;“>=“&A70;$A$2:$A$62;“<"&MONATSENDE(A70;0)) Schöne Grüße, Martin
Hallo…
folgendes Problem: 2 Arbeitsblätter, xvergleich { =XVERGLEICH(D45;’14‘!A1:A1000;0) wunderbar gibt Zeilennummer aus soll aber von Blatt 2 den Text auf der Zeile in Blatt 1 ausgeben, finde bis dato keine Lösung.
Wer kann helfen ???
Hallo Lutz,
wie Du schon gesehen hast, liefert XVERGLEICH (oder auch der normale VERGLEICH) eine relative Position. Um den Inhalt der gefundenen Position auszugeben, musst Du das Ganze mit INDEX kombinieren. Also etwa in der Art:
=INDEX(’14‘!A1:A1000;XVERGLEICH(D45;’14‘!A1:A1000;0))
Schöne Grüße,
Martin
Hallo Martin,
danke für die schnelle Antwort.
Die Sache mit der Verschachtelung mit Index war auch mein 1.Gedanke, leider kommt da nur #NV an.
Nochmals vielen Dank
Gruß Lutz
Hallo Lutz,
wenn die VERGLEICH oder XVERGLEICH-Funktion eine Zeilennummer liefert, dann muss auch der INDEX ein Ergebnis liefern. Zumindest, wenn du die gleichen Zellbezüge verwendest. Liegt es vielleicht daran? Oder liefert auch XVERGLEICH für den konkreten Einzelfall nur ein #NV?
Schöne Grüße,
Martin
Dankeschön, du hast mir wirklich sehr geholfen (INDEX und VERGLEICH).
Das spart mir wirklich sinnloses rumgeklicke. Formeln sind das Beste, ever…
Viele Grüße
Ines
Hallo Ines,
danke, das freut mich zu hören.
Schöne Grüße,
Martin
Hallo Martin,
war mein Anfängerfehler.
Funktionierte sofort nach dem ich den Suchbereich koregiert hatte , habe als letzen Eintrag Begrenzung genommen da funktioniert es nicht nur bei ersten zeile ohne Eintrag sofort Erfolg.
=INDEX(’14‘!B1:B3817;XVERGLEICH(D3;’14‘!A1:A3817;0))
Nochmals vielen Dank
Lutz
Hallo Martin,
Deine Artikel sind echt super, gut zum stöbern und nachschlagen 😀 weiter so!
Allerdings habe ich ein Problem mit einer Daten-Monstrosität bei der ich trotz einiger Erfahrung selber nicht mehr weiterkomme…
Ich habe irgendwo hier in den Kommentaren etwas von Array Formel gelesen aber keine nachvollziehbare Erklärung dazu – und Index-Vergleich-Array ist so ziemlich meine letzte Hoffnung.
Gibt es dazu einen Blogeintrag?
Als Excel-Fan hilft man anderen ja gerne, und jetzt wurde mir eine Tabelle vorgelegt, bei der die Daten in sechs Zeilen dicken Blöcken angelegt sind. Mit Sortieren und Filtern ist erst mal Pustekuchen.
Workaround wäre eine Suchfunktion auf einem anderen Tabellenblatt, nun recherchiere ich seit einer Woche wie ich in Spalten B:U (20 Spalten!) nach einem eindeutigen Wert suche und den in Spalte A (gleiche Zeile) entsprechenden Wert ausgegeben bekomme… Ich stehe derzeit zwischen aufgeben und pro Spalte eine Suche und dann mit WENN und ISTLEER abzufragen ob die Spalte etwas hat… geht das eleganter?
Hallo Jule,
wenn ich es richtig verstehe, besteht in deiner Tabelle ein einzelner Datensatz aus 6 Zeilen. Ich würde für einen solchen Fall zu einer Lösung mit Power Query tendieren. Das ist zwar auch nicht ganz trivial, aber vermutlich einfacher als eine Formellösung. Im Artikel Von Zeilen zu Spalten mit Power Query habe ich kürzlich – an einem zugegebenermaßen sehr einfachen Beispiel – gezeigt, wie man mehrzeilige Datensätze in einzeilige umwandeln kann.
Vielleicht hilft das ja weiter.
Schöne Grüße,
Martin
Hallo,
ich habe einen Sverweis gegen eine Index+Vergleich ersetzt, da mein Suchkriterium >250 Zeichen war und ich gelesen habe, dass dann der Sverweis nicht funktioniert. Leider funktioniert mein Index aber hier auch nicht. Beim Vergleich der Zellen wo es funktioniert/nichjt funktioniert sehe ich bis auf die Länge keinen Unterschied.
Woran kann das liegen oder gibt es für den Index auch eine Zeichenbeschränkung?
Formel: =INDEX(Tabelle1!$B$2:$C$216;VERGLEICH(AC50;Tabelle1!$B$2:$B$216;0);2)
Hallo Thomas,
so wie es aussieht, gibt es auch bei INDEX/VERGLEICH eine Beschränkung auf 255 Zeichen. Der folgende Artikel von Microsoft schlägt eine Lösungsalternative in Form einer Matrix-Formel vor:
https://support.microsoft.com/de-de/office/korrigieren-eines-fehlerwerts-vom-typ-fehler-in-der-sverweis-funktion-1fabc766-32ae-4f7f-a2c4-d095153e6894
Ich habe allerdings nicht ausprobiert, ob das funktioniert.
Schöne Grüße,
Martin