Excel Wert aus Matrix mit 2 Suchkriterien

Diese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein. Projektanfrage perDiese E-Mail-Adresse ist vor Spambots geschützt! Zur Anzeige muss JavaScript eingeschaltet sein. senden

Inhaltsverzeichnis Show

Index-Vergleich mit 2 Kriterien
Index-Vergleich mit mehr als 2 Kriterien
Benötigen Sie einen VBA Programmierer?
Kann Sverweis mehrere Werte ausgeben?
Kann man Sverweis verschachteln?
Wie funktioniert Sverweis mit 2 Tabellen?
Wie funktioniert der Xverweis?

Ich habe eine Tabelle (siehe unten) aus der ich in einer Suche mit 2 Kriterien das Resultat finden muss. Kann ich das mit SVerweis und wer kenn die Formel:

Suchkriterium 1: Finde in einer Tabelle die Zeile, in welcher ein bestimmter Betrag steht

Suchkriterium 2: Und finde nun die Spalte, in der zu diesem Betrag das 2. Kriterium steht.

Ich kenne den Betrag (9'700) und die Tarifstufe (A3) und möchte, dass Excel das Resultat für 9700 = 170 und 1,76 findet.

Mit der normalen Sverweis-Formel finde ich in der Tabelle die Zeile, in der der Suchbetrag von 9700 steht und kann je nach Parametern das Resultat aus den rechtsfolgenden Zellen ausgeben.

Ich benötige jedoch das Resultat aus einer Zelle, welche unter dieser Zeile (9700) mit dem 2. Suchkriterium (A3) in einer anderen Spalte übereinstimmt.

Ergebnis sollte sein: Bei 9700 und Tarifstufe A3 beträgt der Betrag 170 und 1.76%. Diese Werte sollten dann in einer Zelle als Ansicht ausgewiesen werden.

Kennt jemand die Formel(n)? Vielleicht kann das Ergebnis durch eine andere Formel(Funktion) gefunden werden?

Excel hat viele verschiedene Formeln, mehr als eine einzelne Person je verwenden kann. Formeln wie SVERWEIS und die INDEX-, VERGLEICH-Formeln werden am häufigsten verwendet. Eine normale INDEX-VERGLEICH-Formel oder der SVERWEIS sucht einen Wert anhand eines Kriteriums in einer Liste. Aber was ist wenn nach mehr als einem Kriterium gesucht werden soll?

Inhaltsverzeichnis

Index-Vergleich mit 2 Kriterien
Index-Vergleich mit mehr als 2 Kriterien
Benötigen Sie einen VBA Programmierer?

Index-Vergleich mit 2 Kriterien

Die Formel kann weiterhin verwendet werden. Es sind nur ein paar Anpassungen erforderlich.
Wie unten im Screenshot zu sehen ist, haben wir zwei Kriterien, um nach dem Umsatz zu suchen.

Die Formelsyntax lautet:

=INDEX(such_bereich;VERGLEICH(1;INDEX((kriterium1=kriterien_bereich1)*(kriterium2=kriterien_bereich2);0;1);0))

Erläuterung:
Gerne erläutern wir die Formel anhand dieses Beispiels. Wir haben eine Liste mit Namen, Städte und einem Umsatz. Uns interessiert, wieviel Umsatz hat Frau Meier aus Frauenfeld gemacht.

Da es mehrere „Meier“ gibt und mehrere Personen aus Frauenfeld, kann nicht einfach der SVERWEIS oder die „normale“ INDEX/VERGLEICH-Formel verwendet werden. Wir schreiben die Formel so:

Wir verwenden hier die Indexformel, die innerhalb der Vergleich-Formel verwendet wird. Wenn wir innerhalb der Vergleichs-Formel die Index-Formel auswerten, ist Folgendes ersichtlich. WAHR bedeutet, dass das Kriterium gefunden wurde.

Wenn nun der erste Teil der Formel angesehen wird, dann ist ersichtlich, dass in der Liste die ersten drei Namen Treffer sind. Im zweiten Teil der Formel ist ersichtlich, dass der zweite und der fünfte Eintrag ein Treffer ist.

Indem wir beide Arrays multiplizieren, ergibt es nur beim zweiten Eintrag einen übereinstimmenden Treffer.

Dies bedeutet beim zweiten Eintrag ist der gesuchte Wert. Wenn Sie dieses Prinzip verstanden haben, können Sie die Formel nun beliebig erweitern.

Hier ist die Beispiel-Datei zum downloaden: Beispiel mehrere Kriterien Index Vergleich

Index-Vergleich mit mehr als 2 Kriterien

Die Formel kann auch mehr als 2 Kriterien haben. Wir müssen lediglich eine weitere Array in den Index einfügen:

=INDEX(such_bereich;VERGLEICH(1;INDEX((kriterium1=kriterien_bereich1)*(kriterium2=kriterien_bereich2)*(kriterium3=kriterien_bereich3);0;1);0))

Weitere Informationen über Excel-Dropdowns finden Sie hier und Informationen über Web-Apps finden Sie hier.

Benötigen Sie einen VBA Programmierer?

Wir als exact construct programmieren mit einem Team von rd. 20 Mitarbeitern seit über 10 Jahren Excel-Tools. Wir sind ein Nischenanbieter der spezialisiert auf Makros/VBA-Codes ist. Daneben unterstützen wir auch als 3rd Level Support die IT-Abteilungen rund um Probleme bei MS Office (Excel, Word, PowerPoint, etc.).

Haben Sie ein Excel-Problem? Benötigen Sie einen Makro-Programmierer? Rufen Sie uns unverbindlich an +41 52 511 05 25 oder kontaktieren Sie uns via Kontaktformular

Kann Sverweis mehrere Werte ausgeben?

Ist es möglich, bei einem SVERWEIS mehrere Ergebnisse auszugeben?.

Um mehrere Ereignisse nach einer Suche zu erhalten, verwenden Sie die INDEX-Funktion..

Sie benötigen noch weitere Funktionen, mit denen Sie anschließend Ihre Suchanfrage Stück für Stück verknüpfen und zusammenbauen können..

Kann man Sverweis verschachteln?

Erst wenn alle drei WENN-Funktionen eine positive Prüfung liefern, übergibt die dritte WENN-Funktion die entsprechende Zeile an die SVERWEIS-Funktion. Auf diesem Wege können Sie bis zu sieben WENN-Funktionen verschachteln und somit auf bis zu acht Kriterien prüfen.

Wie funktioniert Sverweis mit 2 Tabellen?

Die Funktion SVERWEIS wird immer dann verwendet, wenn Sie mehrere Excel-Tabellen miteinander vergleichen und/oder zusammenfassen wollen. Das S steht hierbei für Senkrecht, denn Excel durchsucht mit dieser Funktion die erste senkrechte Spalte beider Tabellen nach dem angegebenen Zielwert.

Wie funktioniert der Xverweis?

Die XVERWEIS-Funktion durchsucht einen Bereich oder eine Anordnung und gibt dann das Element zurück, das der ersten gefundenen Übereinstimmung entspricht. Wenn keine Übereinstimmung vorhanden ist, kann XVERWEIS zur nächsten (ungefähren) Übereinstimmung zurückkehren.

excel wert aus matrix mit suchkriterien