Suche nach mehreren Kriterien mit INDEX und MATCH
Bei einer großen Datenbank in einer Excel-Tabelle mit mehreren Spalten und Zeilenbeschriftungen ist es immer schwierig, etwas zu finden, das mehrere Kriterien erfüllt. In diesem Fall können Sie eine Matrixformel mit th . verwendene INDEX und PASSEN ZU fsalbungen.
Wie führt man eine Suche mit mehreren Kriterien durch?
Um das herauszufinden PRODUKTE das ist weißen und mittlere-Größe mit einem Preis von $18 Wie in der obigen Abbildung gezeigt, können Sie die boolesche Logik nutzen, um ein Array aus Einsen und Nullen zu generieren, um die Zeilen anzuzeigen, die die Kriterien erfüllen. Die MATCH-Funktion findet dann die Position der ersten Zeile, die alle Kriterien erfüllt. Danach findet INDEX die entsprechende Produkt-ID in derselben Zeile.
Generische Syntax
=INDEX(return_range,MATCH(1,(criteria_value1=criteria_range1*criteria_value2=criteria_range2*(…),0))
√ Hinweis: Dies ist eine Matrixformel, die Sie mit eingeben müssen Ctrl + Verschiebung + Weiter.
- Rückgabebereich: Der Bereich, aus dem die Kombinationsformel die Produkt-ID zurückgeben soll. Bezieht sich hier auf den Produkt-ID-Bereich.
- Kriterien_Wert: Die Kriterien, die verwendet werden, um die Position der Produkt-ID zu finden. Bezieht sich hier auf die Werte in den Zellen H4, H5 und H6.
- Kriterienbereich: Die entsprechenden Bereiche, in denen die Kriterien_Werte aufgeführt sind. Hier bezieht sich auf die Farbe, Größe und Preisklassen.
- match_type 0: Erzwingt MATCH, den ersten Wert zu finden, der genau dem entspricht Lookup-Wert.
Um das Produkt zu finden, das ist weißen und mittlere-Größe mit einem Preis von $18, bitte kopieren oder geben Sie die Formel unten in die Zelle H8 ein und drücken Sie Ctrl + Verschiebung + Weiter um das Ergebnis zu erhalten:
=INDEX(B5: B10,VERGLEICH(1,("White"=C5: C10) * ("Mittel"=D5: D10) * (18=E5: E10),0))
Oder verwenden Sie einen Zellbezug, um die Formel dynamisch zu machen:
=INDEX(B5: B10,VERGLEICH(1,(H4=C5: C10) * (H5=D5: D10) * (H6=E5: E10),0))
Erklärung der Formel
=INDEX(B5:B10,MATCH(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10),0))
- (H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10): Die Formel vergleicht die Farbe in der Zelle H4 gegen alle farben im sortiment C5: C10; vergleicht die größe in H5 gegen alle Größen in D5: D10; vergleicht den Preis in H6 gegen alle Preise in E5: E10. Das erste Ergebnis sieht so aus:
{WAHR;FALSCH;WAHR;FALSCH;WAHR;FALSCH}*{FALSCH;FALSCH;WAHR;WAHR;WAHR;FALSCH}*{FALSCH;FALSCH;FALSCH;WAHR;WAHR;FALSCH}.
Die Multiplikation wandelt die TRUEs und FALSE in 1s und 0s um:
{1;0;1;0;1;0}*{0;0;1;1;1;0}*{0;0;0;1;1;0}.
Nach der Multiplikation haben wir ein einzelnes Array wie folgt:
{0;0;0;0;1;0}. - ÜBEREINSTIMMUNG(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0) = ÜBEREINSTIMMUNG(1,{0;0;0;0;1;0}, 0): Der match_type 0 fordert die MATCH-Funktion auf, die genaue Übereinstimmung zu finden. Die Funktion gibt dann die Position von zurück 1 im Array {0;0;0;0;1;0}, Das ist 5.
- INDEX(B5: B10,ÜBEREINSTIMMUNG(1,(H4=C5:C10)*(H5=D5:D10)*(H6=E5:E10), 0)) = INDEX(B5: B10,5): Die INDEX-Funktion gibt die 5Wert im Produkt-ID-Bereich B5: B10, Das ist 30005.
Verwandte Funktionen
Die Excel-INDEX-Funktion gibt den angezeigten Wert basierend auf einer bestimmten Position aus einem Bereich oder einem Array zurück.
Die Excel MATCH-Funktion sucht in einem Zellbereich nach einem bestimmten Wert und gibt die relative Position des Werts zurück.
Verwandte Formeln
Suchen Sie den nächstgelegenen Übereinstimmungswert mit mehreren Kriterien
In einigen Fällen müssen Sie möglicherweise basierend auf mehr als einem Kriterium den nächstgelegenen oder ungefähren Übereinstimmungswert suchen. Mit der Kombination von INDEX-, MATCH- und IF-Funktionen können Sie dies schnell in Excel erledigen.
Ungefähre Übereinstimmung mit INDEX und MATCH
Es gibt Zeiten, in denen wir ungefähre Übereinstimmungen in Excel finden müssen, um die Leistung der Mitarbeiter zu bewerten, die Ergebnisse der Schüler zu bewerten, das Porto basierend auf dem Gewicht zu berechnen usw. In diesem Tutorial werden wir darüber sprechen, wie die Funktionen INDEX und MATCH zum Abrufen der Ergebnisse, die wir brauchen.
Suchwerte aus einem anderen Arbeitsblatt oder einer anderen Arbeitsmappe
Wenn Sie wissen, wie Sie die SVERWEIS-Funktion verwenden, um nach Werten in einem Arbeitsblatt zu suchen, ist das Vlookup von Werten aus einem anderen Arbeitsblatt oder einer anderen Arbeitsmappe kein Problem für Sie. Das Tutorial zeigt Ihnen, wie Sie Werte aus einem anderen Arbeitsblatt in Excel suchen.
Die besten Tools für die Office-Produktivität
Kutools für Excel - Hilft Ihnen, sich von der Masse abzuheben
Möchten Sie Ihre tägliche Arbeit schnell und perfekt erledigen? Kutools for Excel bietet 300 leistungsstarke erweiterte Funktionen (Arbeitsmappen kombinieren, nach Farbe summieren, Zelleninhalt teilen, Datum konvertieren usw.) und 80% Zeit für Sie sparen.
- Entwickelt für 1500 Arbeitsszenarien, hilft Ihnen bei der Lösung von 80% Excel-Problemen.
- Reduzieren Sie täglich Tausende von Tastatur- und Mausklicks und entlasten Sie Ihre müden Augen und Hände.
- Werden Sie in 3 Minuten ein Excel-Experte. Sie müssen sich keine schmerzhaften Formeln und VBA-Codes mehr merken.
- 30 Tage unbegrenzte kostenlose Testversion. 60 Tage Geld-zurück-Garantie. Kostenloses Upgrade und Support für 2 Jahre.
Office-Registerkarte - Aktivieren Sie das Lesen und Bearbeiten von Registerkarten in Microsoft Office (einschließlich Excel).
- Eine Sekunde, um zwischen Dutzenden offener Dokumente zu wechseln!
- Reduzieren Sie jeden Tag Hunderte von Mausklicks für Sie und verabschieden Sie sich von der Maushand.
- Steigert Ihre Produktivität um 50%, wenn Sie mehrere Dokumente anzeigen und bearbeiten.
- Bringt effiziente Registerkarten in Office (einschließlich Excel), genau wie Chrome, Firefox und New Internet Explorer.
