Sie können die Excel-MATCH-Funktion verwenden, um die relative Position eines bestimmten Werts in einem Zellbereich oder einem Array zu ermitteln.
Die MATCH-Funktion ähnelt der SVERWEIS-Funktion, da beide unter Excel-Such-/Referenzfunktionen kategorisiert sind. SVERWEIS sucht in einer Spalte nach einem bestimmten Wert und gibt einen Wert in derselben Zeile zurück, während die MATCH-Funktion nach einem bestimmten Wert in einem Bereich sucht und die Position dieses Werts zurückgibt.
Die Excel MATCH-Funktion sucht nach einem angegebenen Wert in einem Zellbereich oder einem Array und gibt die relative Position des ersten Auftretens dieses Werts im Bereich zurück. Die MATCH-Funktion kann auch verwendet werden, um mit Hilfe der INDEX-Funktion (genau wie Vlookup) einen bestimmten Wert nachzuschlagen und den entsprechenden Wert zurückzugeben. Sehen wir uns an, wie Sie die Excel-MATCH-Funktion verwenden, um die Position eines Nachschlagewerts in einem Zellbereich zu finden.
Excel MATCH-Funktion
Die MATCH-Funktion ist eine integrierte Funktion in Excel und wird hauptsächlich zum Auffinden der relativen Position eines Nachschlagewerts in einer Spalte oder Zeile verwendet.
Syntax der MATCH-Funktion:
=MATCH(lookup_value,lookup_array,[match_type})Wo:
Lookup-Wert - Der Wert, den Sie in einem angegebenen Zellbereich oder in einem Array suchen möchten. Dies kann ein numerischer Wert, ein Textwert, ein logischer Wert oder ein Zellbezug mit einem Wert sein.
lookup_array – Die Arrays von Zellen, in denen Sie nach einem Wert suchen. Es muss eine einzelne Spalte oder eine einzelne Zeile sein.
match_type – Dies ist ein optionaler Parameter, der auf 0,1 oder -1 gesetzt werden kann und der Standardwert ist 1.
- 0 sucht nach einer genauen Übereinstimmung, gibt einen Fehler zurück, wenn sie nicht gefunden wird.
- -1 sucht nach dem kleinsten Wert, der größer oder gleich lookup_value ist, wenn das Lookup-Array in aufsteigender Reihenfolge angezeigt wird.
- 1 sucht nach dem größten Wert, der kleiner oder gleich dem look_up-Wert ist, wenn das Lookup-Array in absteigender Reihenfolge angezeigt wird.
Finden Sie die Position einer genauen Übereinstimmung
Nehmen wir an, wir haben den folgenden Datensatz, in dem wir die Position eines bestimmten Werts finden möchten.
In dieser Tabelle möchten wir die Position eines Städtenamens (Memphis) in Spalte (A2:A23) finden, also verwenden wir diese Formel:
=VERGLEICH("memphis",A2:A23,0)Das dritte Argument wird auf ‚0‘ gesetzt, da wir eine genaue Übereinstimmung mit dem Städtenamen finden möchten. Wie Sie sehen, ist der Städtename „memphis“ in der Formel klein geschrieben, während in der Tabelle der erste Buchstabe des Städtenamens groß geschrieben ist (Memphis). Die Formel ist jedoch in der Lage, die Position des angegebenen Werts im angegebenen Bereich zu finden. Dies liegt daran, dass bei der MATCH-Funktion die Groß-/Kleinschreibung nicht beachtet wird.
Notiz: Wenn der lookup_value nicht im Lookup-Bereich gefunden wird oder wenn Sie den falschen Lookup-Bereich angeben, gibt die Funktion den Fehler #N/A zurück.
Sie können im ersten Argument der Funktion anstelle eines direkten Werts einen Zellbezug verwenden. Die folgende Formel ermittelt die Position des Werts in Zelle F2 und gibt das Ergebnis in Zelle F3 zurück.
Finden Sie die Position einer ungefähren Übereinstimmung
Es gibt zwei Möglichkeiten, nach einer ungefähren oder genauen Übereinstimmung des Nachschlagewerts zu suchen und seine Position zurückzugeben.
- Eine Möglichkeit besteht darin, den kleinsten Wert zu finden, der größer oder gleich dem angegebenen Wert ist (nächstgrößte Übereinstimmung). Dies kann erreicht werden, indem das letzte Argument (match_type) der Funktion auf ‚-1‘ gesetzt wird.
- Eine andere Möglichkeit ist der größte Wert, der kleiner oder gleich (nächstkleinste Übereinstimmung) dem angegebenen Wert ist. Dies kann erreicht werden, indem der match_type der Funktion auf „1“ gesetzt wird.
Nächstkleinste Übereinstimmung
Wenn die Funktion keine genaue Übereinstimmung mit dem angegebenen Wert findet, wenn der Übereinstimmungstyp auf '1' gesetzt ist, sucht sie den größten Wert, der etwas kleiner ist als der angegebene Wert (d. h. der nächstkleinere Wert) und gibt seine Position zurück . Damit dies funktioniert, müssen Sie das Array in aufsteigender Reihenfolge sortieren, andernfalls führt dies zu einem Fehler.
Im Beispiel verwenden wir die folgende Formel, um die nächstkleinere Übereinstimmung zu finden:
=VERGLEICH(F2,D2:D23,1)Wenn diese Formel die genaue Übereinstimmung mit dem Wert in Zelle F2 nicht finden konnte, zeigt sie auf die Position (16) des nächstkleineren Werts, d. h. 98.
Nächstgrößtes Spiel
Wenn der Übereinstimmungstyp auf „-1“ gesetzt ist und die MATCH-Funktion keine genaue Übereinstimmung finden kann, findet sie den kleinsten Wert, der größer als der angegebene Wert ist (d. h. der nächstgrößere Wert) und gibt seine Position zurück. Das Lookup-Array muss für diese Methode in absteigender Reihenfolge sortiert werden, andernfalls wird ein Fehler zurückgegeben.
Geben Sie beispielsweise die folgende Formel ein, um die nächstgrößere Übereinstimmung mit dem Nachschlagewert zu finden:
=VERGLEICH(F2,D2:D23,-1)Diese MATCH-Funktion sucht nach dem Wert in F2 (55) im Lookup-Bereich D2:D23, und wenn sie die genaue Übereinstimmung nicht finden kann, gibt sie die Position (16) des nächstgrößeren Werts, d. h. 58, zurück.
Wildcard-Match
Platzhalter können in der MATCH-Funktion nur verwendet werden, wenn match_type auf „0“ gesetzt ist und der Nachschlagewert eine Textzeichenfolge ist. Es gibt Platzhalter, die Sie in der MATCH-Funktion verwenden können: ein Sternchen (*) und ein Fragezeichen (?).
- Fragezeichen (?) wird verwendet, um ein einzelnes Zeichen oder einen Buchstaben mit der Textzeichenfolge abzugleichen.
- Stern (*) wird verwendet, um eine beliebige Anzahl von Zeichen mit der Zeichenfolge abzugleichen.
Zum Beispiel haben wir zwei '?'-Platzhalter im lookup_value (Lo??n) der MATCH-Funktion verwendet, um einen Wert zu finden, der der Textzeichenfolge mit zwei beliebigen Zeichen (an den Platzhalterstellen) entspricht. Und die Funktion gibt die relative Position des übereinstimmenden Werts in Zelle E5 zurück.
=VERGLEICH("Lo??n",A2:A22,0)
Sie können den Platzhalter (*) auf die gleiche Weise wie (?) verwenden, aber ein Sternchen wird verwendet, um eine beliebige Anzahl von Zeichen abzugleichen, während ein Fragezeichen für ein einzelnes Zeichen verwendet wird.
Wenn Sie beispielsweise „sp*“ verwenden, könnte die Funktion mit Lautsprecher, Geschwindigkeit oder Spielberg usw. übereinstimmen. Wenn die Funktion jedoch mehrere/doppelte Werte findet, die dem Suchwert entsprechen, gibt sie nur die Position des ersten Werts zurück.
Im Beispiel haben wir im Argument lookup_value „Kil*o“ eingegeben. Die Funktion MATCH() sucht also nach einem Text, der am Anfang ‚Kil‘, am Ende ‚o‘ und dazwischen beliebig viele Zeichen enthält. 'Kil*o' stimmt mit dem Kilimanjaro im Array überein und daher gibt die Funktion die relative Position des Kilimanjaro zurück, die 16 ist.
INDEX und MATCH
MATCH-Funktionen werden selten allein verwendet. Sie werden oft mit anderen Funktionen kombiniert, um leistungsstarke Formeln zu erstellen. Wenn die MATCH-Funktion mit der INDEX-Funktion kombiniert wird, kann sie erweiterte Lookups durchführen. Viele Leute ziehen es immer noch vor, SVERWEIS zu verwenden, um einen Wert zu suchen, weil es einfacher ist, aber INDEX MATCH ist flexibler und schneller als SVERWEIS.
SVERWEIS kann einen Wert nur vertikal nachschlagen, d. h. Spalten, während die INDEX MATCH-Kombination sowohl vertikale als auch horizontale Lookups durchführen kann.
INDEX-Funktion zum Abrufen eines Werts an einer bestimmten Stelle in einer Tabelle oder einem Bereich. Die MATCH-Funktion gibt die relative Position eines Werts in einer Spalte oder Zeile zurück. Bei Kombination findet der MATCH die Zeilen- oder Spaltennummer (Position) eines bestimmten Werts, und die INDEX-Funktion ruft einen Wert basierend auf dieser Zeilen- und Spaltennummer ab.
Syntax der INDEX-Funktion:
=INDEX(array,row_num,[col_num],)Wie auch immer, sehen wir uns an einem Beispiel an, wie INDEX MATCH funktioniert.
Im folgenden Beispiel möchten wir die Punktzahl „Quiz2“ für die Schülerin „Anne“ abrufen. Dazu verwenden wir die folgende Formel:
=INDEX(B2:F20,VERGLEICH(H2,A2:A20,0),3)INDEX benötigt eine Zeilen- und Spaltennummer, um einen Wert abzurufen. In der obigen Formel findet die verschachtelte MATCH-Funktion die Zeilennummer (Position) des Wertes „Anne“ (H2). Dann liefern wir diese Zeilennummer an die INDEX-Funktion mit einem Bereich B2:F20 und einer Spaltennummer (3), die wir angeben. Und die INDEX-Funktion gibt den Wert „91“ zurück.
Zwei-Wege-Suche mit INDEX und MATCH
Sie können auch die Funktionen INDEX und MATCH verwenden, um einen Wert in einem zweidimensionalen Bereich zu suchen (Zwei-Wege-Suche). Im obigen Beispiel haben wir die Funktion MATCH verwendet, um die Zeilennummer eines Werts zu finden, aber wir haben die Spaltennummer manuell eingegeben. Aber wir können sowohl Zeile als auch Spalte finden, indem wir zwei MATCH-Funktionen verschachteln, eine im Argument row_num und eine andere im Argument column_num der INDEX-Funktion.
Verwenden Sie diese Formel für eine bidirektionale Suche mit INDEX und MATCH:
=INDEX(A1:F20,VERGLEICH(H2,A2:A20,0),VERGLEICH(H3,A1:F1,0))Wie wir wissen, kann die MATCH-Funktion sowohl horizontal als auch vertikal nach einem Wert suchen. In dieser Formel findet die zweite MATCH-Funktion im colum_num-Argument die Position von Quiz2 (4) und liefert sie an die INDEX-Funktion. Und der INDEX ruft die Punktzahl ab.
Jetzt wissen Sie, wie Sie die Match-Funktion in Excel verwenden.