In diesem Artikel erfahren Sie, wie Sie mehrere Instanzen eines Werts in Excel nachschlagen.
Werte mit der Dropdown-Option suchen?
Hier verstehen wir, wie wir mit der INDEX-Funktionsmatrixformel verschiedene Ergebnisse nachschlagen können. Wählen Sie einfach den Wert aus der Liste aus und das entsprechende Ergebnis wird angezeigt.
Generische Formel
| {=INDEX(array,SMALL(IF(lookup_value=lookup_value_range,ROW(lookup_value_range)-ROW(erste Zelle von lookup_value_range)+1),ROW(1:1)))} |
Anordnung: Der Bereich, aus dem Sie Daten abrufen möchten.
Lookup-Wert: Ihr lookup_value, den Sie filtern möchten.
lookup_value_range: Der Bereich, in dem Sie lookup_value filtern möchten.
Die erste Zelle im lookup_value-Bereich: Wenn Ihr lookup_value-Bereich $A$5:$A$100 ist, dann sind es $A$5.
Wichtig: Alles soll sein absolut referenziert. Lookup-Wert kann je nach Anforderung relativ sein.
Geben Sie es als Matrixformel ein. Verwenden Sie nach dem Schreiben der Formel STRG+UMSCHALT+EINGABETASTE, um sie zu einer Matrixformel zu machen.
Beispiel für die Suche nach mehreren Ergebnissen
Ich habe diese Schülerdaten in Reichweite A2:E14. In Zelle G1 habe ich eine Dropdown-Liste mit Regionswerten, z. Mitte, Osten, Norden, Süden und Westen. Jetzt möchte ich, egal welche Region ich in G1 habe, eine Liste aller Schüler aus dieser Region sollte in der H-Spalte angezeigt werden.
Um mehrere Werte in Excel nachzuschlagen, identifizieren wir unsere Variablen.
Anordnung: $C$2:$C$14
Lookup-Wert : $G$1
lookup_value_range : $ 2 $: $ 14 $
Die erste Zelle des lookup_value-Bereichs: $2$
Gemäß den obigen Daten lautet unsere Formel zum Abrufen mehrerer Werte in Excel:
| {=IFERROR(INDEX($C$2:$C$14,SMALL(IF($G$1=$A$2:$A$14,ROW($A$2:$A$14)-ROW($A$2)+1) ,ROW(1:1))),"Kein Wert mehr")} |
Dies ist eine Array-Formel. Verwenden Sie nicht nur die Eingabetaste, nachdem Sie die Formel eingegeben haben. Verwenden Sie STRG + UMSCHALT + EINGABETASTE zusammen.
Lassen Sie uns nun verstehen, WIE ES FUNKTIONIERT.
Auch wenn die Formel komplex aussehen mag, die Idee ist einfach. Wir müssen die Indexnummer jedes Vorkommens von Wert abrufen und dann Werte mit der INDEX-Funktion von Excel abrufen.
Daher besteht die größte Herausforderung darin, ein Array von Indexnummern von . zu erhalten Lookup-Wert. Um Indexnummern zu erhalten, haben wir die Funktionen IF und ROW verwendet. Die Formel ist in der Tat insgesamt komplex, lassen Sie uns sie aufschlüsseln.
Wir möchten Werte aus der studentischen Spalte erhalten, also ist unser Array für die INDEX-Funktion $C$2:$C$14.
Jetzt müssen wir Zeilennummern von $ 2 $: $ 14 $ (Nachschlagewert), in dem der Wert von G1 existiert (vorerst sagen wir G1 hat eine Zentrale).
WENN($G$1=$A$2:$A$14,ROW($A$2:$A$14): Dieser Teil gibt nun die Zeilennummer zurück, wenn der Wert einer Zellenanzahl von G1 (zentral) im Bereich $ 2 $: $ 14 $ sonst kehrt zurück FALSCH. In diesem Beispiel wird es zurückgegeben
{2;FALSCH;FALSCH;FALSCH;FALSCH;7;8;FALSCH;FALSCH;11;FALSCH;FALSCH;FALSCH}.
Da das obige Array Zeilennummern ab der 1. Zeile enthält (1:1) und wir Zeilen ab unserem Array (A2:A14) benötigen. Dazu verwenden wir -ROW($A$2)+1 in der IF-Formel. Dies gibt eine Reihe von Zeilen zurück, bevor unser Array gestartet wird.
Für dieses Beispiel ist es -1. Wenn es von A3 aus starten würde, würde es -2 zurückgeben und so weiter. Diese Zahl wird von jeder Zahl im von IF zurückgegebenen Array abgezogen. So endlich WENN($G$1=$A$2:$A$14,ZEILE($A$2:$A$14)-REIHE($A$2)+1) das wird übersetzt in {1;FALSCH;FALSCH;FALSCH;FALSCH;6;7;FALSCH;FALSCH;10;FALSCH;FALSCH;FALSCH}.
Als nächstes wird dieses Array von der Funktion SMALL umgeben. Diese Funktion gibt den N-kleinsten Wert im angegebenen Array zurück. Jetzt haben wir SMALL({2;FALSCH;FALSCH;FALSCH;FALSCH;7;8;FALSCH;FALSCH;11;FALSCH;FALSCH;FALSCH},ZEILE(1:1)). ROW(1:1) gibt 1 zurück. Daher gibt die obige Funktion den ersten kleinsten Wert im Array zurück, der 2 ist.
Wenn Sie diese Formel in die unteren Zellen kopieren, wird ROW(1:1) zu ROW(2:2) und gibt den zweitkleinsten Wert im Array zurück, der 7 ist und so weiter. Dadurch kann die Funktion den ersten gefundenen Wert zuerst zurückgeben. Wenn Sie jedoch zuerst den zuletzt gefundenen Wert abrufen möchten, verwenden Sie die LARGE-Funktion anstelle der SMALL-Funktion.
Die INDEX-Funktion verwendet jetzt die Werte, die über den Funktionen zurückgegeben wurden, und gibt problemlos jeden übereinstimmenden Wert aus einem Bereich zurück.
Sie können auch genaue Übereinstimmungen mit der INDEX- und MATCH-Funktion in Excel suchen. Erfahren Sie mehr darüber, wie Sie die Groß-/Kleinschreibung mit der Funktion INDEX & MATCH in Excel durchführen. Sie können auch mit den Platzhaltern in Excel nach den Teilübereinstimmungen suchen.
Ich hoffe, dieser Artikel zum Nachschlagen mehrerer Instanzen eines Werts in Excel ist erklärend. Weitere Artikel zum Berechnen von Werten und zugehörigen Excel-Formeln finden Sie hier. Wenn Ihnen unsere Blogs gefallen haben, teilen Sie sie mit Ihren Freunden auf Facebook. Und Sie können uns auch auf Twitter und Facebook folgen. Wir würden uns freuen, von Ihnen zu hören, lassen Sie uns wissen, wie wir unsere Arbeit verbessern, ergänzen oder erneuern und für Sie verbessern können. Schreiben Sie uns auf der E-Mail-Site.
Verwenden Sie SVERWEIS aus zwei oder mehr Nachschlagetabellen | Um aus mehreren Tabellen zu suchen, können wir einen IFERROR-Ansatz verwenden. Um aus mehreren Tabellen nachzuschlagen, wird der Fehler als Wechsel zur nächsten Tabelle verwendet. Eine andere Methode kann ein If-Ansatz sein.
So führen Sie die Groß-/Kleinschreibung in Excel durch | Die SVERWEIS-Funktion von Excel unterscheidet nicht zwischen Groß- und Kleinschreibung und gibt den ersten übereinstimmenden Wert aus der Liste zurück. INDEX-MATCH ist keine Ausnahme, kann jedoch geändert werden, um die Groß-/Kleinschreibung zu berücksichtigen. Mal sehen wie…
Nachschlagen von häufig erscheinendem Text mit Kriterien in Excel | Die Suche erscheint am häufigsten im Text in einem Bereich, den wir mit der Funktion INDEX-MATCH mit MODE verwenden. Hier ist die Methode.
Populäre Artikel :
So verwenden Sie die IF-Funktion in Excel : Die IF-Anweisung in Excel überprüft die Bedingung und gibt einen bestimmten Wert zurück, wenn die Bedingung WAHR ist, oder einen anderen bestimmten Wert, wenn FALSE.
So verwenden Sie die SVERWEIS-Funktion in Excel : Dies ist eine der am häufigsten verwendeten und beliebtesten Funktionen von Excel, die verwendet wird, um Werte aus verschiedenen Bereichen und Blättern zu suchen.
So verwenden Sie die SUMIF-Funktion in Excel : Dies ist eine weitere wesentliche Funktion des Dashboards. Dies hilft Ihnen, Werte unter bestimmten Bedingungen zusammenzufassen.
So verwenden Sie die ZÄHLENWENN-Funktion in Excel : Zählen Sie Werte mit Bedingungen mit dieser erstaunlichen Funktion. Sie müssen Ihre Daten nicht filtern, um bestimmte Werte zu zählen. Die Countif-Funktion ist unerlässlich, um Ihr Dashboard vorzubereiten.