Excel INDEX- und VERGLEICH-Funktionen kombinieren

>>>>>>>>Excel INDEX- und VERGLEICH-Funktionen kombinieren

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:

  1. Vergleich-Funktion
  2. Index-Funktion
  3. 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.

Beispiel herunterladen

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
Index und Vergleich Funktionen verschachteln

Tipp, um Userfehler zu vermeiden

Index und Vergleich mit Dropdown

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)

Beispiel mit Dropdown herunterladen

Artikel bewerten

Excel INDEX- und VERGLEICH-Funktionen kombinieren
Artikel bewerten
4.5 Sternen
52 Stimmen
Finden Sie den Artikel „Excel INDEX- und VERGLEICH-Funktionen kombinieren“ hilfreich? Dann hinterlassen Sie uns einen Kommentar, oder bewerten Sie ihn.

Von |2018-03-16T14:04:18+00:0022.04.16|Excel, Microsoft, Office|10 Kommentare

10 Comments

  1. Holgo 18. Oktober 2018 um 12:51 Uhr - Antworten

    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?

  2. Dave 25. September 2018 um 14:49 Uhr - Antworten

    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!

  3. Martin 9. August 2018 um 11:28 Uhr - Antworten

    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“.

  4. Ralf 12. Januar 2018 um 14:57 Uhr - Antworten

    Die Beschreibung ist leicht und verständlich aufgebaut und liefert die perfekte Lösung für das Problem. Vielen Dank.

  5. Sonja 11. Januar 2018 um 18:24 Uhr - Antworten

    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?

    • 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

  6. nata 211 4. September 2017 um 19:12 Uhr - Antworten

    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

  7. Margarete 22. August 2017 um 10:30 Uhr - Antworten

    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

  8. Ingo 10. August 2017 um 13:47 Uhr - Antworten

    Sehr gut erklärt, somit sofort durchschaubar.

  9. HAY 8. Juni 2017 um 10:45 Uhr - Antworten

    Danke

Hinterlassen Sie einen Kommentar