Excel Index (Vergleich) Funktion – “SVerweis” für Profis

Sie haben wahrscheinlich von der SVerweis Funktion bereits gehört. Der SVerweis ist mit Sicherheit die meistgebrauchte Suchfunktion von Excel.

Nur wenige Excel Anwender kennen jedoch die Index/Vergleich Kombination. Dies ist eine flexiblere Methode einzelne Zellen in einer definierten Matrix wiederzugeben.

Die Funktion löst auch eine der grössten SVerweis Limitierungen: Man kann nicht nur von links nach rechts suchen, sondern auch von rechts nach links und oben/unten.

Excel Index Funktion

Wie bereits erwähnt brauchen wir eine Kombination aus zwei Funktionen um die Index Funktion zum Absuchen von beliebigen Werten zu verwenden. Die erste ist die Index Funktion selbst.

Excel-index-vergleich

Wie Sie im Formel Syntax unten sehen, mit Index können Sie eine Zelle genau „orten“ in dem Sie erst den Suchbereich (Matrix) angeben und dann die Anzahl der Zeilen und Spalten.

Index Syntax

Excel-index-syntax

Excel Vergleich Funktion

Die Index Funktion allein ist nur bedingt hilfreich. Man muss eine bestimmte Zeilen- und Spaltenanzahl angeben um den gewünschten Wert zu ermitteln. Um das ganz dynamischer zu gestalten, verwenden wir die Vergleich Funktion um die Spalten- oder Zeilennummer zu finden.

Excel-vergleich

Im Beispiel oben suchen wir die Zeilennummer von Filiale 8.
Wichtig: Dies ist die Zeile relativ zum Suchbereich, d.h. im Suchbereich B4:B14, Filiale 8 befindet sich in der 3. Zeile.

Vergleich Syntax

Excel-vergleich-syntax

Excel Index Vergleich kombiniert

Um nun die beiden Funktionen zusammenzubringen, werden wir mit Index beginnen und dann mit Hilfe von Vergleich die Zeilenzahl ermitteln.

Excel-index-vergleich-final

Vorteile gegenüber dem SVerweis

Der Vorteil der Index/Vergleich Kombination liegt darin, dass es im Prinzip keine Grenzen gibt. Man kann den Suchbereich beliebig absuchen, nicht nur von der ersten Spalte nach rechts wie im SVerweis. Im Beispiel oben, könnten Sie auch einfach den Standort mit dem Umsatz von 185.000 Euro ermitteln. In diesem Fall den Suchbereich in der Vergleich Funktion auf E4:E14 umstellen, das Suchkriterium von 8 auf 185.000 und dann noch die Spaltenzahl von 4 auf 2 stellen.

File:U+21FE.gif Bei Fragen zum Index/Vergleich, einfach im neuen Excel Forum vorbeischauen. Wir helfen gerne!
File:U+21FE.gif Ein weiteres Beispiel zum Thema Index / Verleich finden Sie hier.

19 Comments

  • Guten Tag,

    ich weiß nicht, ob ich etwas falsch mache, aber wenn ich die Tabelle identisch aufbau, bekomme ich als Ergebnis der Formel

    =INDEX(B4:E14;VERGLEICH(8;B4:E14;0);4)

    den Wert #NV. Was ist hier die Ursache?

  • Max

    Das Problem liegt beim Vergleich. Es muss eine Spalte oder Zeile abgesucht werden, also einfach den letzten Teil umschreiben: VERGLEICH(8;B4:B14;0);4)

    • Hallo Max,
      bei mir wird auch #NV ausgegeben.
      Was meinst du mit umschreiben?

      Bei mir erfolgt erst die Ausgabe wenn in Spalte A ein x steht,
      sonnst kommt #NV
      Wie kann ich anstatt von #NV einen Text ausgeben?

      • Andreas

        Hallo André,

        #NV ist ein Fehler, der immer dann auftritt, wenn eine Bedingung für die Formel fehlt. Das kann so wie bei dir ein Eintrag sein.
        Das kannst du aber folgendermaßen umgehen:

        =wennfehler(hier kommt deine komplette Formel rein);““)

        Wird dein Ergebnis als Teil einer anderen Formel gebraucht, dann ersetze die „“ durch eine Null oder nimm irgendwelchen Text, den du gerade brauchst. Allerdings muss der Text komplett in „“ geschrieben werden.

        Gruß Andreas

  • Ach, na klar, jetzt seh ich es. Danke!

    Michael

  • Danke für die Anregung; trifft schon fast mein Problem, leider wird immer nur ein Ergebnis oder Wert gesucht – ich brauche aber alle Werte zum Suchkriterium:
    Stelle Dir vor, die Spalten LAND und FILIALE sind vertauscht;
    Ich möchte jetzt als Ergebnis alle FILIALEN aus LAND=Deutschland, bestenfalls untereinander gelistet – wie muss dann die Formel aussehen ?.

    Danke und Gruß/Andreas

    • Aus meiner Sicht der Dinge wird das Ganze beser mit einer Datenbankfunktion zu lösen sein!

      DBSUMME

      Vielleicht hilft Dir dieser Ansatz.

      Beste Grüße

      Lars

  • *push*

    Ich hätte auch Bedarf an einer Antwort auf Andreas Frage. Ich möchte ebenfalls eine lange Liste nach mehreren Kriterien durchsuchen und dazugehörende Werte oder die Zeilennummer des gefundenen Werts zurückgeben lassen.

    Viele Grüße,
    Malte

    • Andreas

      Hallo Malte,

      stellst du deine Frage bitte ins Forum und lädst eine kleine Beispieltabelle hoch? Da kann dir besser geholfen werden.

      Gruß Andreas

  • Super! Vielen Dank, diese Formel hat mir sehr geholfen. Der SVERWEIS der links suchen kann …. perfekt!

  • Wie geht die Formel für „In welchem Land liegt Pilsen“??
    Ich komme nicht drauf

  • Hätte da auch noch ne Frage. Habe in Tabellenblatt 1 in A1 z.B. „René Adler Nummer 1“ stehen. In Tabellenblatt 2 habe ich nun eine Liste mit allen Spielern des HSV, allerdings nur die Nachnamen. Nun möchte ich in B1 eine Formel haben, die meine Tabelle auf Tabellenblatt 2 mit A1 auf Tabellenblatt 1 vergleicht und mir dann den jeweiligen Nachnamen auswirft.

    Heißt in B1 soll später nur Adler stehen.

    Das müsste ich doch auch per sverweis hinbekommen, oder? Habe es mit verleich, suchen, index, … versucht. Es klappt nur einfach nichts.

    Hoffe ihr könnt mir irgendwie einen Tipp geben. Vorweg schonmal vielen dank <3

  • Ich hab auch so eine Tabellenfrage:
    Könnten Sie mir bitte bei folgende Fragestellung behilflich sein ?

    Ich möchte gern eine Folge von Zahlen in einzelne Excel-Kästchen umsortieren und krieg es einfach nicht hin.

    Beispiel:
    Die Folge 6 7 56 8 9 3 44 58 59 4 2 1 soll in einzelne Zellen wie folgt eingetragen werden. 6 7 5 6 8 9 3 4 4 5 8 5 9 4 2 1, also jede Ziffer in eine eigene Zelle aber
    aufeinander folgende Zellen, ohne Leerzellen.

    Und auch die andere Richtung soll funktionieren,
    wenn eine 4 oder eine 5 in den einzelnen Zellen auftaucht, soll jeweils die folgende Zelle verbunden werden

    Beispiel:
    Die Folge 2 3 4 6 4 5 7 5 4 5 8 8 3 7
    soll werden zu 2 3 46 45 7 54 58 8 3 7

    Mit der Bitte um eine kräftigen Denkanstoß….

    • Andreas

      Hallo Andreas,

      wie ist denn deine Ausgangsfolge geschrieben? In einer Zelle oder pro Zahl alles extra? Sind die Zahlenfolgen unterschiedlich lang? Und sollen bei einer 4 oder 5 die Zellen verbunden werden oder sollen beide Zahlen in einer Zelle stehen? Wenn wirklich die Zellen verbunden werden sollen, geht es nur über eine VBA-Programmierung.

      beste Grüße Andreas

  • frage ist es möglich bei einem Excel ein vergleih machen zu lassen das in der 3 spalte ein ja oder nein angezeigt wird ?
    Blau Gross:AT | ABC| DFE Ja
    rot klein:BP | DEV | CEE nein
    grün Gross:BP | FQA | AT nein
    lila Gross:AT | ABC| DFE ja
    Braun klein:BP | PROD | AT ja
    schwartz klein:BP | PROD | CEE ja
    es muss der genaue Text verglichen werde ? also ich habe eine Tabelle1 wo mehrere verschidene Texte stehen wie das z.B. klein:BP | PROD | CEE und eine Tabelle2 wo dann in Spalte A schwartz und in Spalte B „klein:BP | PROD | CEE“ das steht. Nun soll in Spalte C angegeben werden ob der Text aus Tabelle1 in der Tabelle 2 in der Spalte B vor handen ist und das mit einem Ja oder nein in Tabelle 2 in der Spallte C.
    Ist so etwas möglich ? Wenn ja wie macht man das ?
    Besten Dank Vür Informationen

    • Andreas

      Hallo Peter,

      um Missverständnissen vorzubeugen, was meinst du mit klein und groß? Bezieht sich das auf den Text, also bp oder BP?
      Am besten du kommst ins Forum, stellst dort deine Frage und lädst auch eine Beispieltabelle hoch.

      beste Grüße Andreas

  • Hallo Leute,

    ich habe folgende Matrix:

    A B C D E F G H
    1 3 6 8 0 2
    4
    4 4
    4

    Gibt es die Möglichkeit, nach dem letzten Wert in der Zeile zu suchen und die Spaltenüberschrift dieses Wertes auszugeben?

    Ich habe das Gefühl die Lösung liegt sehr nahe der INDEX-VERGLEICH-Funktion.

    Vielen Dank schonmal.

    • Andreas

      Hallo Dardan,
      kann es in der Zeile zu leeren Zellen kommen, dass erst in der übernächsten der nächste Eintrag steht?
      beste Grüße Andreas

    • Andreas

      Hallo Dardan,
      ich habe deine Daten in A1 übertragen.
      Nimm diese Formel: =INDIREKT(ADRESSE(1;ANZAHL2(A2:H2)))
      Funktioniert einwandfrei solange keine leeren Zellen dazwischen sind.
      beste Grüße Andreas

Frage oder Kommentar zu diesem Artikel?