Die INDEX- und VERGLEICH-Funktionen in Excel zu kombinieren, ist bei fortgeschrittenen Anwendern eine der am häufigsten verwendeten Methoden, um eine Tabelle zu durchsuchen. Dadurch kann sogar das Problem der SVERWEIS-Funktion mit negativem Spaltenindex gelöst werden.
Wer schon mit Matrix-Funktionen gearbeitet hat, wird mit der Kombination aus INDEX- und VERGLEICH-Funktionen eine hervorragende Dynamik entdecken.
Dieser Artikel ist Teil einer Trilogie:
- Vergleich-Funktion
- Index-Funktion
- Index- und Vergleich-Funktion kombinieren
Unser Beispiel zur Kombination von Index- und Vergleich-Funktionen
Die Quartaltabelle (A1 bis D6) enthält Spaltenbezeichnungen (Jan, Feb, Mrz) UND Zeilenbezeichnungen (Warp Antrieb, Darth Vader Helm, …), deshalb handelt es sich hier um eine Matrix.
Was wir wollen: wenn ein User in A9 einen Monat (hier „Feb“) und in A11 eine Bezeichnung (hier „Big Kahuna Burger“) eingibt, soll in A13 das Ergebnis aus der Quartaltabelle geliefert werden (hier 32). Eine Kombination aus INDEX- und VERGLEICH-Funktionen wird uns dabei helfen.
Wenn Sie noch keine Erfahrung mit diesen Funktionen haben, können Sie unsere Artikel über die INDEX-Funktion und die VERGLEICH-Funktion lesen.
Index- und Vergleich-Funktionen verschachteln
Sie wollen, dass Excel in der Zelle B13 den Wert 32 ausgibt, weil sich dieser Wert an der Schnittstelle der „Big Kahuna Burger“-Zeile und der „Feb“-Spalte befindet. Dafür klicken Sie die Zelle A13 an und geben Folgendes ein:
=INDEX(A1:D6;VERGLEICH(A11;A1:A6;0);VERGLEICH(A9;A1:D1;0))
Drücken Sie die ENTER-Taste und Excel gibt Ihnen 32 aus.
Die Index-Funktion hat 3 Argumente:
- Matrix: (A1:D6) – das ist der Bereich, in dem nach einem Wert gesucht wird
- Zeile: VERGLEICH(A11;A1:A6;0) – das ist die Zeile, die die Index-Funktion durchsuchen soll. Dieser Wert wird anhand einer Vergleich-Funktion ermittelt
- Spalte: VERGLEICH(A9;A1:D1;0) – das ist die Spalte, die die Index-Funktion durchsuchen soll. Dieser Wert wird ebenso anhand einer Vergleich-Funktion ermittelt
Tipp, um Userfehler zu vermeiden
Hier treten häufig Probleme auf, wenn User sich vertippen oder unnötige Leerzeichen eingeben. Sollten Sie Fehleinträge vermeiden wollen, können Sie die Eingabe über einen Dropdown-Feld (Auswahliste) steuern. Dadurch muss in A9 und in A11 nichts mehr per Hand eingegeben, sondern lediglich ein Eintrag ausgewählt werden (s. Bild links).
Excel gibt Ihnen die Möglichkeit, ein Dropdown-Feld über „Datenüberprüfung“ zu erstellen. Mehr dazu erfahren Sie in unserem Artikel: Dropdown-Liste erstellen (Auswahlliste)
Vielleicht noch ein Hinweis:
Der „Beginn“ der Matrix muss in allen 3 Matrizen gleich sein! Hier ist das bei =INDEX(A1:D6;VERGLEICH(A11;A1:A6;0);VERGLEICH(A9;A1:D1;0)) gegeben (Immer A1 als erstes Feld) An meiner eigenen Excel bin ich verzweifelt, bis das passte.
Um „#NV“ Felder anders auszugeben, nutze ich auch bei SVERWEIS gerne die WENNFEHLER Formel.
In diesem Falle wäre das:
=WENNFEHLER(INDEX(A1:D6;VERGLEICH(A11;A1:A6;0);VERGLEICH(A9;A1:D1;0));0)
So erscheint statt #NV eine 0
Hallo,
ich habe eine xlsx-Datei (ca. 20MB) in EXCEL 2016 erstellt, welche sehr viele Arbeitsblätter (ca. 120 Stück) enthält.
Das erste Arbeitsblatt ist eine Tabelle mit ca. 150 Spalten und 130 Zeilen.
Alle folgenden Arbeitsblätter sind Formulare welche sich per SVERWEIS aus dem ersten Datenblatt die Werte holen.
Für jeden neuen Auftrag wir ein neues Arbeitsblatt angelegt welches wieder per SVERWEIS auf das erste Arbeitsblatt zugreift und Werte ausließt.
Das Ganze hat auch bis zum 119. Arbeitsblatt funktioniert, ab dem 120. Arbeitsblatt ließ sich die Datei noch speichern aber nicht mehr öffnen.
Eine WWW-Suche nach dem Problem deutet darauf hin das die Funktion SVERWEIS sehr viel Rechenleistung und Speicher benötigt und ab einer bestimmten Menge von SVERWEIS mit Berechnungen nicht mehr hinterherkommt.
Dies ist die Formel
=SVERWEIS($AF$10;Blatt1!$D$5:$BT$109;SPALTEN(Blatt1!$D:$H);FALSCH)
die in jedem Tabellenblatt ca. 20-mal aufgerufen wird.
Kann ich diese Formel mit INDEX- und VERGLEICH-Funktionen ersetzten und würden diese dann das oben beschriebene Problem nicht verursachen?
Oder kann ich einen anderen Weg gehen um das Problem zu lösen?
Vielen Dank.
Hallo,
ich empfehle das System zu prüfen. Ist Excel wirklich das beste Tool für die Aufgabe? Für mich hört es sich nach einer typischen Datenbank-Aufgabe an.
O
Zitat
„kibasi 6. Februar 2018 um 9:11 Uhr – Antworten
Wenn der Matrixpart in die geschachtelte Funktion =WENN(ISTLEER(Prüfzelle);““;Matrixteil) eingebettet ist, erscheint die Anzeige nicht mehr“
Ich habe auch das Problem, dass, wenn das Feld mit dem Suchkriterium leer ist, mir „#NV“ ausgegeben wird. Ansonsten funktioniert meine Formel mit Index und Vergleich sehr gut. Wie bekomme ich es hin, dass in dem geschilderten Fall bei einem leeren Suchfeld im Ergebnis 0 ausgegeben wird? Mit dem Kommentar von kibasi kann ich nichts anfangen. Meine Formel lautet: =INDEX($V$2:$V$14;VERGLEICH(H2&I2;$T$2:$T$14&$U$2:$U$14;0);)
Wo muss ich ggf. noch ein ;0 einsetzen?
Hallo,
ich hatte mich mehere Stunden mit dieser Thematik beschäftigt und bin dann auf Ihre Seite gestoßen. Innerhalb von 10-15 Minuten habe ich das Problem lösen können. Herzlichen Dank!
Sie wollen, dass Excel in der Zelle B13 den Wert 32 ausgibt, weil sich dieser Wert an der Schnittstelle der „Big Kahuna Burger“-Zeile und der „Feb“-Spalte befindet. Dafür klicken Sie die Zelle A13 an und geben Folgendes ein:
>> Es müsste „A13“ heißen und nicht „B13“.
Die Beschreibung ist leicht und verständlich aufgebaut und liefert die perfekte Lösung für das Problem. Vielen Dank.
Hallo, ich habe eine funktionierende Index/Vergleich-Funktion, die mir allerdings #NV anzeigt, wenn das Suchkriteriumfeld leer ist. Ich habe versucht sie in eine Wenn-Funktion einzubinden, aber dann funktioniert der Matrixpart nicht mehr. Wie kann ich es machen, dass sie nur berechnet, wenn auch etwas im ersten Feld ist?
Wenn der Matrixpart in die geschachtelte Funktion =WENN(ISTLEER(Prüfzelle);““;Matrixteil) eingebettet ist, erscheint die Anzeige nicht mehr
Hallo,
kann mir jemand helfen z.B.:
ich habe eine spalte mit zahlen 25,15,50,10,75 (die spalte kann nicht sortiert werden)
Ich suche 8, die kann er nicht finden dann soll er mit die nächste große zahl finden, das ist die 10.
wie schreibe ich die Formel dazu.
danke für eure hilfe
Exzellente Erklaerung, endlich habe ich etwas verstanden von der Index-Match Funktion.
Ausserdem sehr hilfreich, dass ich die Exceldatei nicht abschreiben musste, sondern diese gleich als download verfuegbar war.
Danke
Sehr gut erklärt, somit sofort durchschaubar.
Danke