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)
Artikel bewerten
Finden Sie den Artikel „Excel INDEX- und VERGLEICH-Funktionen kombinieren“ hilfreich? Dann hinterlassen Sie uns einen Kommentar, oder bewerten Sie ihn.
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