Wie mit mehreren Kriterien INDEX und VERGLEICH in Excel SVERWEIS
Mit INDEX und MATCH SVERWEIS ersetzen
Was wir das Modell und die Verschiebung zu sehen zugleich wirklich in der Lage brauchen, ist zu sein. MATCH ist eine Funktion, die Ihnen die Position eines Elements in einem Array gibt. Die Syntax für MATCH ist wie folgt:
Die lookup_value ist das, was Sie suchen.
Die Suchmatrix ist die Anordnung von Werten Sie versuchen, die lookup_value in zu finden.
Der optionale match_type bestimmt, ob MATCH die lookup_value genau das finden, muss (mit einem 0) oder am nächsten kommt zurück, die, bevor sie kommt (mit einem 1) oder nach (mit einer -1) alphanummerisch.
Die Index-Funktion nimmt eine Position und gibt den Wert, der in der Zelle ist. Die Syntax für INDEX ist wie folgt:
Das Array ist die Tabelle der Daten, die die Zelle von Ihnen gewünschten Wert enthält.
Die row_num ist die relative Zeilennummer der Zelle, die Sie wollen.
Die col_num ist die relative Spaltennummer der Zelle, die Sie wollen.
Durch die Kombination von INDEX und MATCH können wir das gleiche Ergebnis wie VLOOKUP produzieren. Mit der gleichen suchen wir für SVERWEIS tat. die INDEX / MATCH Paar sieht wie folgt aus:
C4: H1159 ist die Anordnung, dass INDEX verwendet den Wert zu finden.
A2 ist die Zelle, die den Wert, den wir MATCH finden möchten.
C4: C1159 ist die Suchmatrix, dass MATCH durch sieht den Wert in A2 zu finden.
Die 0 bedeutet, dass MATCH für den genauen Wert aussehen wird statt einer annähernden ein.
Spalte 6 ist die Säule im C4: H1159array, dass die kombinierte Kraftstoffverbrauch Werte hält.
Das Ergebnis ist identisch mit dem SVERWEIS Ergebnis. MATCH findet die erste kombinierte Kraftstoffverbrauch Wert für die Acura ILX, das heißt, es 38 MPG für die 1,5 statt einer der anderen Motorvarianten geben. Um ein bestimmtes Modell und Motor Hubraum Kombination zu finden, müssen wir unsere INDEX / MATCH Formel in eine Matrixformel ändern.
Holen Sie sich die neuesten Excel Tipps und Tricks von den Newsletter Mitglied werden!
Verbinden Sie den Newsletter auf die neuesten Artikel zu bleiben. Registrieren und Sie erhalten einen kostenlosen Leitfaden mit 10 zeitsparenden Tastenkombinationen bekommen!
187 Gedanken zum Thema „Wie mit mehreren Kriterien INDEX und MATCH vlookup“
Vielen Dank für den großen Beitrag über die Verwendung des kombinierten INDEX # 038; MATCH-Funktionen. Es wird sicherlich nützlich für mich in der Zukunft. Leider fällt es nur kurz, um ein Problem zu lösen, ich jetzt arbeite. Können Sie, wie dieser Ansatz (oder einen anderen Ansatz vollständig) für die Verwendung mit zwei Kriterien modifiziert werden könnte, von denen ist nicht richtig? Insbesondere mag ich ein Array suchen, wo ein Wert eine exakte Übereinstimmung ist, und der zweite Wert in einem Bereich (angegeben durch zwei Säulen - eine obere und eine untere Grenze). Wenn diese beiden Kriterien erfüllt sind, möchte ich den Wert in einer Spalte neben den Werten ausgeben, die durchsucht wurden. Zum Beispiel in dem Bild angehängt, markierten die Zellen gelb sind, was ich möchte eine Formel erzeugen (hier habe ich sie mit der Hand bestimmt) zu erzeugen. Ich brauche Spalte A für eine Übereinstimmung für Spalte F suchen, und dann Fälle finden, wo Spalte G in Spalten B zwischen den Werten fällt - C. Dann möchte ich die Ausgabe enthalten ist der Wert in Spalte D. Bitte lassen Sie mich wissen, wenn Sie würde wie jede Klärung, und dank für jede Hilfe, die Sie anbieten können.
Chuck Trese sagt:
Nur müssen erkennen, dass SieRyan Carroll sagt:
In meinem Szenario verwende ich G2 als Datum und möchten Sie das Datum am nächsten zu dem Zeitpunkt, zu G2 vorgesehen abzurufen. Im Idealfall muss ich die genaue Übereinstimmung von dem Zeitpunkt G2 abzurufen, aber manchmal gibt es keine exakte Übereinstimmung sein. Wenn dies der Fall ist, muß ich den nächsten Termin im Bereich abgerufen werden, ob es vor oder nach dem Tag, an G2 aufgeführt ist. Ist eine Formel für diese machbar? Vielen Dank!
Diese Formel (INDEX = ($ A $ 1: $ D $ 7, MATCH (1, ($ A $ 1: A 7 $ $ $ = F2) * ($ B $ 1: B $ $ 7<$G2),0),4)) works, however I need to retrieve the closest date
Chuck Trese sagt:
sorry, Text-Editor wird nicht zulassen, Größer-als und weniger als Symbole.
Sie müssen nur erkennen, dass Sie ‚Größer-als‘ und ‚weniger als‘ verwenden können, nicht nur ‚gleich im Vergleich überprüft. (Leider ist die Gleichung in der früheren Post ist ein Chaos, weil es nicht die Größer-als nicht zeigen oder weniger als Symbole, sondern entfernt in der Mitte der Formel -. Der Teil, der am wichtigsten)
Chuck Vorschlag ein Größer-als zu verwenden, oder weniger als obere und untere Grenzen zu setzen, ist direkt auf dem Geld. Seine Formel prüft sicher Spalte B zu machen, ist kleiner als die Spalte G Punkt. Um genauer zu sein, können Sie die obere auch gebunden überprüfen, etwa so:
Vergessen Sie nicht als Matrixformel mit STRG + SHIFT + ENTER eingeben!
sai krishna sagt:
Ich habe ein Blatt mit Namen in einer Spalte und in einem anderen Blatt mit Namen und Nummern. Ich habe verwendet vlookup, um die Anzahl von Blatt 2 entsprechenden Wert in Blatt zu erhalten 1. Die Schwierigkeit i bin vor in Blatt 2 gleiche Namen gibt es für verschiedene Zahlen, so vlookup den erste Spiel Wert zu geben und den Rest zu verlassen. Hilf mir, dieses Problem zu lösen
Ich hatte das gleiche Problem. So wie ich das Problem gelöst ist „2" hinter dem Nachnamen des zweiten Eintrags für die Person (en) mit zwei Zahlen, und in der Ergebnistabelle hinzuzufügen, haben Sie für die Menschen, zwei Einträge haben, um „2 Hinzufügen "hinter dem Nachnamen.
Wenn jemand weiß, eine bessere Lösung ...
Vielen Dank für diese tolle Anleitung. Endlich verstehe ich, wie den Index + Match-Funktion verwendet und konnte es in einer komplexen Tabelle für die Arbeit nutzen. Am Ende hatte ich die Daten von einer vertikal strukturierten Tabelle zu einem horizontal gegliederten einem transponieren. Gibt es eine Möglichkeit Index + Match für Daten zu verwenden, die von oben nach unten strukturiert ist? So oder so, ein großes Tutorial und gut geschrieben Blog. Mach weiter so!
Vielen Dank - das ist eine große Hilfe ist. Ich konnte die Formel mit 3 Variablen tun. Mein einziges Problem ist, ich mag diese Formel nach unten einer ganzen Spalte von 100K + Positionen kopieren. Ich bin nicht in der Lage, nur die untere rechte Ecke klicken, weil ich einen BEZUG bekommen! Fehler. Die Formel Kopien mit „#REF!“, Wo das Spiel Bereich sein sollen. Der einzige Weg, die ich bekommen kann es zu arbeiten, um die STRG + SHIFT + Hit in jeder Zelle ENTER, aber ich kann diese 100K + mal nicht. Jede einfache Möglichkeit, diese Formel nach unten einer ganzen Spalte zu kopieren?
Herzlichen Glückwunsch an die Formel zum Laufen! Matrixformeln (die, die Sie mit STRG + SHIFT eingeben + ENTER) sind ein wenig pingelig, wie Sie sie kopieren. Sie können die Zelle mit der eingegebenen Formel auswählen und mit der rechten unteren Ecke nach unten ziehen Sie die Formel zu kopieren. Sie können auch die Formel in andere Zellen kopieren und einfügen, aber Sie müssen die ursprüngliche Zelle aus der Paste Auswahl ausschließen oder Excel einen Fehler geben.
Als beiseite, werde ich Sie warnen, dass ein 3-Kriterien INDEX / MATCH über 100k Zellen angewendet wird wahrscheinlich eine sehr lange Zeit in Anspruch nehmen (in der Größenordnung von Stunden) zu berechnen. Achten Sie darauf, Ihre Arbeit zu sichern, bevor die Formel nach unten zu kopieren und zu planen, dies zu tun, wenn Sie in der Lage sind Excel allein zu lassen für mehrere Stunden zu verarbeiten.
Viel Glück!
Andrew
alberto Jaume sagt:
Dies ist ein sehr schöner Artikel / Tutorial! Ich gefunden habe, kann es in untersch Umständen verwendet werden, aber ich habe mich gefragt, ob Sie eine weitere Erklärung in Bezug auf eine particualr Situation zur Verfügung stellen können. Ich versuche, einen Bericht zu erstellen, die für einen Namen in der Tabelle überprüft (das wird mehrere Male wiederholt) und wählt den den neuesten Eintrag (jeder Eintrag ein anderes Datum in einer anderen Spalte). I ma brechen meinen Kopf versuchen, Ihren Ansatz zu verwenden, aber nicht sicher, wie es könnte es funktioniert. Könnten Sie etwas Licht auf diese werfen?
Danke für die Rückmeldung! Diese Methode sollte das erste Spiel immer wieder zurückkehrt es findet, so dass, wenn Sie Ihre Liste-Art vor, so dass neuere Einträge am Anfang erscheinen, soll die Suche, wie Sie wollen arbeiten.
David Elms sagt:
Hallo, ich fragte mich, ob es eine Möglichkeit war das irgendwie mit dem MAX-Befehl in der Formel zu tun?
Ich versuche, im Wesentlichen die gleiche Sache zu erreichen, aber nicht wollen, jemanden haben, in zu gehen und neu sortieren Sie die Daten in dem Blatt die ganze Zeit als die INDEX / MATCH-Lookup in einem anderen Blatt auf die Quelldaten verwendet wird, . Ist es möglich?
Hallo,
Ich denke, es ist eine einfache Möglichkeit, es zu tun (aber Sie müssen eine neue Spalte in Ihrem Datenblatt)
Bitte überprüfen Sie unten.
Ich fusionierte die Zellen, die ich in Kriterien verwenden möchten, und setzen Sie sie in Spalte C zusammen (= A6 # 038;“„# 038; B6)
Dann verwendet diese Formel es zu entsprechen. = VLOOKUP (G6 # 038;“„# 038; H6, $ C $ 6: $ D $ 11,2, FALSE)
Das ist es. Sie können sogar so viele Kriterien erstellen, wie Sie wollen.
Hallo,
Ich denke, es ist eine einfache Möglichkeit, es zu tun (aber Sie müssen eine neue Spalte in Ihrem Datenblatt)
Bitte überprüfen Sie unten.
Ich fusionierte die Zellen, die ich in Kriterien verwenden möchten, und setzen Sie sie in Spalte C zusammen (= A6-“„-B6)
Dann verwendet diese Formel es zu entsprechen. = VLOOKUP (G6-“„-H6, $ C $ 6: $ D $ 11,2, FALSE)
Das ist es. Sie können sogar so viele Kriterien erstellen, wie Sie wollen.
Das ist sehr cool. Danke für das Teilen. Ich hatte viel Glück mit ctrl R und ctrl D kopieren nach unten und nach rechts. Ich wusste nicht, über IFERROR. Ich benutze regelmäßig = IF (ISERROR (Formel),““, Formel) Rohlinge zu erhalten, anstatt # N / A oder # Wert. Prost.
Ich habe ein ähnliches Problem wie Terry; es ist schon komisch, denn wenn ich auf dem Fx Symbol klicken, wird das Ergebnis, das ich will zeigt aber die Zelle immer noch sagt, # N / A. Ich habe auf dem Symbol geklickt daneben sie zu analysieren und sie sagt, dass erster Teil der MATCH-Funktion (in der Formel, es ist die C4: C1159-Bereich) ist das Problem, aber ich kann nicht feststellen, warum.
Irgendwelche Gedanken oder Ideen? Ich würde gerne in der Lage sein, dies zu verketten die beiden Feldern, ohne auf gemeinsam zu tun den Look-up-Wert zu schaffen!
Danke im Voraus!
Einmal, geben Sie die Formel in der Zelle, nicht drücken Sie die Eingabetaste. stattdessen Drücken Sie STRG + SHIFT + ENTER Excel zu sagen, dass es sich um eine Matrixformel. Es wird nur dann funktionieren. Sonst wird es halten # N / A geben.
Hoffe, das war nützlich.
Jay ... vielen Dank! Wir haben in den beiden Feldern den Wert verketten und dann tun SVERWEIS ... was nicht gerade ideal ist. Nun, da ich diese Arbeit bekommen konnte, werde ich mit meinen Kollegen teilen!
Das ist ein großer Info, Dank sooo viel. das ist genau das, was ich brauchte.
Ich habe einen Test auf diesem und es funktionierte perfekt, aber alle meine Daten waren auf 1 Blatt.
dann habe ich versucht, es zu meiner eigentlichen Arbeitsmappe anwenden, die Daten auf 2 Blatt hat, und erhielt den Fehler ‚die Formel, die Sie enthält einen Fehler getippt‘. hier ist meine wirkliche Formel:
der Fehler wahrscheinlich aus den ‚11‘ am Ende der Formel zu kommen. Ich denke, es weiß nicht, dass die 11 bis 11. Spalte in meiner angegebenen Tabelle bezieht sich auf meine ‚Linie Details‘ Seite. Ich habe versucht, ‚Line-Details‘ vor den 11 setzen und das hat nicht funktioniert.
irgendwelche hilfreiche Hinweise? Danke im Voraus!
Ich habe eine Master-Tabelle mit Spalten A bis U und die Daten auf den Zeilen 2 bis 900. würde Ich mag alle Spalte / Zeile-Daten auf einer Kriterien ziehen und Anzeige Ergebnisse (die in der Spalte O und einer von 5 Optionen gefunden würde) mit die gleichen Spaltenüberschriften als Master, sondern in einem anderen Register der Arbeitsmappe. Können Sie mir bitte sagen, ob dies möglich ist?
Avraham Nadell sagt:
Guter Eintrag. Vielen Dank Andrew.
In meiner Quelldaten habe ich in einem Stichtag für jede Änderung der Preise, die das letzte Datum des Inkrafttretens der Preise war. Die Basis effektive Rate war 10 Jahre in der Zukunft.
Die in der Indexformel suchte ich Werte, wo das Datum war> = das Datum ich suchte.
Funktionierte sehr gut, nur ist das Array schwer auf Berechnungen.
Glücklich zu teilen, wenn jemand mehr Infos will
Avraham