So erstellen Sie eine Excel-Suchformel mit mehreren Kriterien

Was Sie wissen sollten

• Erstellen Sie zuerst eine INDEX-Funktion und starten Sie dann die verschachtelte MATCH-Funktion, indem Sie das Lookup_value-Argument eingeben.
• Fügen Sie als Nächstes das Argument Lookup_array hinzu, gefolgt vom Argument Match_type, und geben Sie dann den Spaltenbereich an.
• Wandeln Sie dann die verschachtelte Funktion in eine Matrixformel um, indem Sie Strg + Umschalt + Eingabetaste drücken. Fügen Sie abschließend die Suchbegriffe zum Arbeitsblatt hinzu.

In diesem Artikel wird erläutert, wie Sie eine Nachschlageformel erstellen, die mehrere Kriterien in Excel verwendet, um mithilfe einer Matrixformel Informationen in einer Datenbank oder Datentabelle zu finden. Die Array-Formel beinhaltet das Verschachteln der MATCH-Funktion innerhalb der INDEX-Funktion. Die Informationen umfassen Excel für Microsoft 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010 und Excel für Mac.

Folgen Sie dem Tutorial

Um den Schritten in diesem Lernprogramm zu folgen, geben Sie die Beispieldaten in die folgenden Zellen ein, wie in der Abbildung unten gezeigt. Die Zeilen 3 und 4 bleiben leer, um Platz für die Matrixformel zu schaffen, die in diesem Lernprogramm erstellt wurde. (Beachten Sie, dass dieses Tutorial nicht die Formatierung enthält, die im Bild zu sehen ist.)

• Geben Sie den oberen Datenbereich in die Zellen D1 bis F2 ein.
• Geben Sie den zweiten Bereich in die Zellen D5 bis F11 ein.

Erstellen Sie eine INDEX-Funktion in Excel

Die INDEX-Funktion ist eine der wenigen Funktionen in Excel, die mehrere Formen hat. Die Funktion hat ein Array-Formular und ein Referenz-Formular. Das Array-Formular gibt die Daten aus einer Datenbank oder Datentabelle zurück. Das Referenzformular gibt die Zellreferenz oder Position der Daten in der Tabelle an.

In diesem Lernprogramm wird das Array-Formular verwendet, um den Namen des Lieferanten für Titan-Widgets zu finden, und nicht den Zellbezug zu diesem Lieferanten in der Datenbank.

Gehen Sie folgendermaßen vor, um die INDEX-Funktion zu erstellen:

1. Wählen Sie die Zelle F3 aus, um sie zur aktiven Zelle zu machen. In diese Zelle wird die verschachtelte Funktion eingegeben.
2. Gehen Sie zu Formeln.

   

3. Wählen Sie Lookup & Reference, um die Dropdown-Liste der Funktion zu öffnen.
4. Wählen Sie INDEX aus, um das Dialogfeld „ Argumente auswählen“ zu öffnen.
5. Wählen Sie array,row_num,column_num.
6. Wählen Sie OK aus, um das Dialogfeld Funktionsargumente zu öffnen. In Excel für Mac wird der Formelgenerator geöffnet.
7. Platzieren Sie den Cursor im Textfeld Array.
8. Markieren Sie die Zellen D6 bis F11 im Arbeitsblatt, um den Bereich in das Dialogfeld einzugeben.
   Lassen Sie das Dialogfeld Funktionsargumente geöffnet. Die Formel ist noch nicht fertig. Sie werden die Formel in den Anweisungen unten vervollständigen.
   

Starten Sie die verschachtelte MATCH-Funktion

Wenn Sie eine Funktion in einer anderen verschachteln, ist es nicht möglich, den Formelgenerator der zweiten oder verschachtelten Funktion zu öffnen, um die erforderlichen Argumente einzugeben. Die verschachtelte Funktion muss als eines der Argumente der ersten Funktion eingegeben werden.

Bei der manuellen Eingabe von Funktionen werden die Argumente der Funktion durch ein Komma voneinander getrennt.

Der erste Schritt zur Eingabe der verschachtelten MATCH-Funktion besteht darin, das Lookup_value-Argument einzugeben. Der Lookup_value ist der Ort oder die Zellreferenz für den Suchbegriff, der in der Datenbank abgeglichen werden soll.

Der Lookup_value akzeptiert nur ein Suchkriterium oder einen Suchbegriff. Um nach mehreren Kriterien zu suchen, erweitern Sie den Suchwert, indem Sie zwei oder mehr Zellbezüge mit dem kaufmännischen Und-Symbol (&) verketten oder verbinden.

1. Platzieren Sie im Dialogfeld Funktionsargumente den Cursor im Textfeld Row_num.
2. Geben Sie VERGLEICH(.
3. Wählen Sie Zelle D3 aus, um diesen Zellbezug in das Dialogfeld einzugeben.
4. Geben Sie & (das kaufmännische Und) nach dem Zellbezug D3 ein, um einen zweiten Zellbezug hinzuzufügen.
5. Wählen Sie Zelle E3 aus, um den zweiten Zellbezug einzugeben.
6. Geben Sie , (ein Komma) nach dem Zellbezug E3 ein, um die Eingabe des Lookup_value-Arguments der MATCH-Funktion abzuschließen.

   
   Im letzten Schritt des Tutorials werden die Lookup_values ​​in die Zellen D3 und E3 des Arbeitsblatts eingetragen.

Vervollständigen Sie die verschachtelte MATCH-Funktion

Dieser Schritt behandelt das Hinzufügen des Lookup_array-Arguments für die verschachtelte MATCH-Funktion. Das Lookup_array ist der Zellbereich, den die MATCH-Funktion durchsucht, um das Lookup_value-Argument zu finden, das im vorherigen Schritt des Tutorials hinzugefügt wurde.

Da im Lookup_array-Argument zwei Suchfelder identifiziert wurden, muss dasselbe für das Lookup_array getan werden. Die MATCH-Funktion durchsucht nur ein Array nach jedem angegebenen Begriff. Um mehrere Arrays einzugeben, verwenden Sie das kaufmännische Und, um die Arrays miteinander zu verketten.

1. Platzieren Sie den Cursor am Ende der Daten im Textfeld Row_num. Der Cursor erscheint nach dem Komma am Ende des aktuellen Eintrags.
2. Markieren Sie die Zellen D6 bis D11 im Arbeitsblatt, um den Bereich einzugeben. Dieser Bereich ist das erste Array, das die Funktion durchsucht.
3. Geben Sie & (ein kaufmännisches Und) nach den Zellbezügen D6:D11 ein. Dieses Symbol bewirkt, dass die Funktion zwei Arrays durchsucht.
4. Markieren Sie die Zellen E6 bis E11 im Arbeitsblatt, um den Bereich einzugeben. Dieser Bereich ist das zweite Array, das die Funktion durchsucht.
5. Geben Sie , (ein Komma) nach dem Zellbezug E3 ein, um die Eingabe des Lookup_array-Arguments der MATCH-Funktion abzuschließen.

   

6. Lassen Sie das Dialogfeld für den nächsten Schritt im Lernprogramm geöffnet.

Fügen Sie das MATCH-Type-Argument hinzu

Das dritte und letzte Argument der MATCH-Funktion ist das Match_type-Argument. Dieses Argument teilt Excel mit, wie der Lookup_value mit Werten im Lookup_array abgeglichen werden soll. Die verfügbaren Optionen sind 1, 0 oder -1.

Dieses Argument ist optional. Wenn es weggelassen wird, verwendet die Funktion den Standardwert 1.

• Wenn Match_type = 1 oder weggelassen wird, findet MATCH den größten Wert, der kleiner oder gleich dem Lookup_value ist. Die Lookup_array-Daten müssen in aufsteigender Reihenfolge sortiert werden.
• Wenn Match_type = 0, findet MATCH den ersten Wert, der gleich dem Lookup_value ist. Die Lookup_array-Daten können in beliebiger Reihenfolge sortiert werden.
• Wenn Match_type = -1, findet MATCH den kleinsten Wert, der größer oder gleich dem Lookup_value ist. Die Lookup_array-Daten müssen in absteigender Reihenfolge sortiert werden.

Geben Sie diese Schritte nach dem im vorherigen Schritt eingegebenen Komma in der Zeile Row_num in der Funktion INDEX ein:

1. Geben Sie 0 (eine Null) nach dem Komma in das Textfeld Row_num ein. Diese Zahl bewirkt, dass die verschachtelte Funktion exakte Übereinstimmungen mit den in den Zellen D3 und E3 eingegebenen Begriffen zurückgibt.
2. Geben Sie ) (eine schließende runde Klammer) ein, um die MATCH-Funktion abzuschließen.

   

3. Lassen Sie das Dialogfeld für den nächsten Schritt im Lernprogramm geöffnet.

Beenden Sie die INDEX-Funktion

Die MATCH-Funktion ist fertig. Es ist an der Zeit, zum Textfeld Column_num des Dialogfelds zu wechseln und das letzte Argument für die INDEX-Funktion einzugeben. Dieses Argument teilt Excel mit, dass die Spaltennummer im Bereich D6 bis F11 liegt. In diesem Bereich findet es die von der Funktion zurückgegebenen Informationen. In diesem Fall ein Lieferant für Titan-Widgets.

1. Platzieren Sie den Cursor im Textfeld Column_num.
2. Geben Sie 3 (die Zahl drei) ein. Diese Zahl weist die Formel an, nach Daten in der dritten Spalte des Bereichs D6 bis F11 zu suchen.

   

3. Lassen Sie das Dialogfeld für den nächsten Schritt im Lernprogramm geöffnet.

Erstellen Sie die Array-Formel

Bevor Sie das Dialogfeld schließen, wandeln Sie die verschachtelte Funktion in eine Matrixformel um. Dieses Array ermöglicht der Funktion, nach mehreren Begriffen in der Datentabelle zu suchen. In diesem Tutorial werden zwei Begriffe abgeglichen: Widgets aus Spalte 1 und Titan aus Spalte 2.

Um eine Matrixformel in Excel zu erstellen, drücken Sie gleichzeitig die Tasten STRG, UMSCHALT und EINGABE. Nach dem Drücken wird die Funktion von geschweiften Klammern umgeben, was darauf hinweist, dass die Funktion jetzt ein Array ist.

1. Wählen Sie OK aus, um das Dialogfeld zu schließen. Wählen Sie in Excel für Mac Fertig aus.
2. Wählen Sie Zelle F3 aus, um die Formel anzuzeigen, und platzieren Sie dann den Cursor am Ende der Formel in der Formelleiste.
3. Um die Formel in ein Array umzuwandeln, drücken Sie STRG + UMSCHALT + EINGABE.
4. In Zelle F3 wird ein #NV -Fehler angezeigt. Dies ist die Zelle, in der die Funktion eingegeben wurde.
5. Der #Nv-Fehler wird in Zelle F3 angezeigt, da die Zellen D3 und E3 leer sind. D3 und E3 sind die Zellen, in denen die Funktion sucht, um den Lookup_value zu finden. Nachdem diesen beiden Zellen Daten hinzugefügt wurden, wird der Fehler durch Informationen aus der Datenbank ersetzt.

   

Fügen Sie die Suchkriterien hinzu

Der letzte Schritt besteht darin, die Suchbegriffe zum Arbeitsblatt hinzuzufügen. Dieser Schritt entspricht den Begriffen Widgets aus Spalte 1 und Titan aus Spalte 2.

Wenn die Formel eine Übereinstimmung für beide Begriffe in den entsprechenden Spalten in der Datenbank findet, gibt sie den Wert aus der dritten Spalte zurück.

1. Wählen Sie die Zelle D3 aus.
2. Geben Sie Widgets ein.
3. Wählen Sie die Zelle E3 aus.
4. Geben Sie Titan ein und drücken Sie die Eingabetaste.
5. Der Name des Lieferanten, Widgets Inc., erscheint in Zelle F3. Dies ist der einzige aufgeführte Anbieter, der Titanium Widgets verkauft.
6. Wählen Sie die Zelle F3 aus. Die Funktion erscheint in der Formelleiste über dem Arbeitsblatt. In diesem Beispiel gibt es nur einen Lieferanten für Titan-Widgets. Wenn es mehr als einen Lieferanten gegeben hat, wird der zuerst in der Datenbank aufgeführte Lieferant von der Funktion zurückgegeben.
   
{=INDEX(D6:F11,MATCH(D3&E3,D6:D11&E6:E11,0),3)}