Erste teilweise Übereinstimmung einer Zahl suchen

Manchmal müssen Sie die Position der ersten teilweisen Übereinstimmung ermitteln, bei der eine bestimmte Zahl innerhalb eines Bereichs numerischer Werte in Excel enthalten ist. In einem solchen Fall hilft Ihnen eine VERGLEICH-- und TEXT-Formel, die Sternchen (*) – also Platzhalter für beliebige Zeichen – verwendet. Falls Sie zudem den genauen Wert an dieser Position benötigen, können Sie die INDEX-Funktion zur Formel hinzufügen.

Position der ersten teilweisen Übereinstimmung einer Zahl ermitteln
Erste teilweise Übereinstimmung einer Zahl abrufen

Position der ersten teilweisen Übereinstimmung einer Zahl ermitteln

Um die Position der ersten teilweisen Übereinstimmung einer Zahl zu erhalten, die „345“ enthält, verwenden Sie eine Kombination aus der VERGLEICH- und der TEXT-Funktion zusammen mit Platzhaltern – und zwar folgendermaßen: Um beliebige Zahlen zu finden, die „345“ enthalten, setzen Sie „345“ zwischen zwei Sternchen (*). Diese Operation wandelt den numerischen Wert jedoch in einen Textwert um. Daher müssen Sie die TEXT-Funktion nutzen, um die Zahlen im Bereich in Text umzuwandeln – nur so kann die VERGLEICH-Funktion die Position der teilweisen Übereinstimmung korrekt ermitteln.

Allgemeine Syntax

=MATCH(„*"&)number&„*",TEXT()lookup_array,"0"),0)

√ Hinweis: Dies ist eine Matrixformel, die mit Strg+Umschalttaste+Eingabetasteeingegeben werden muss.

Um die Position der ersten Übereinstimmungszahl zu erhalten, die „345“ enthält, kopieren oder geben Sie die folgenden Formeln in die Zelle E6 ein und drücken Sie Strg+Umschalttaste+Eingabetaste, um das Ergebnis zu erhalten:

=VERGLEICH(„*"&)345&„*";TEXT()B5:B16;"0");0)

Oder verwenden Sie Eine Zellreferenz, um die Formel dynamisch zu gestalten:

=VERGLEICH(„*"&)E5&„*";TEXT()B5:B16;"0");0)

√ Hinweis: Um Eine Zellreferenz/Zahl und Text miteinander zu verknüpfen, müssen Sie ein kaufmännisches Und-Zeichen (&) dazwischen einfügen. Der Text muss außerdem in Anführungszeichen gesetzt werden.

Erläuterung der Formel

=INDEX()"*"&345&„*",TEXT(B5:B16,"0"),0)

• TEXT(B5:B16;"0"): Die TEXT-Funktion wandelt alle numerischen Werte in B5:B16 in Text mit dem Formatcode „0“ um. Dadurch erhalten wir ein Text-Array wie folgt: {"56445";"21354";"84265";"54342";"34545";"45632";"87954";"68546";"34567";"75681";"33587";"16467"}.
  Klicken Sie hier, um mehr über die TEXT-Funktion zu erfahren.
• INDEX()"*"&345&„*",TEXT(B5:B16;"0"),0) = INDEX("*"&345&„*" Der Suchwert "*"&345&„*" findet beliebige Textzeichenfolgen, die „345“ enthalten – egal, an welcher Position sich „345“ innerhalb der Zeichenfolge befindet. Mit dem Vergleichstyp 0 weist die VERGLEICH-Funktion an, die Position des ersten exakt übereinstimmenden Werts im Array zu ermitteln. Daher gibt VERGLEICH 5 zurück.

Erste teilweise Übereinstimmung einer Zahl abrufen

Um die erste teilweise Übereinstimmung einer Zahl basierend auf der von VERGLEICH bereitgestellten Position (siehe unten) abzurufen, können wir die INDEX-Funktion verwenden.

Allgemeine Syntax

=INDEX(MATCH())return_range,"*"&number&„*",TEXT()lookup_array,"0"),0))

√ Hinweis: Dies ist eine Matrixformel, die mit Strg+Umschalttaste+Eingabeeingegeben werden muss.

Um die erste Übereinstimmungszahl zu erhalten, die „345“ enthält, kopieren oder geben Sie die folgenden Formeln in die Zelle E7 ein und drücken Sie Strg+Umschalttaste+Eingabetaste, um das Ergebnis zu erhalten:

=INDEX(VERGLEICH())B5:B16,"*"&345&„*";TEXT()B5:B16;"0");0))

Oder verwenden Sie Eine Zellreferenz, um die Formel dynamisch zu gestalten:

=INDEX(VERGLEICH())B5:B16,"*"&E5&„*";TEXT()B5:B16;"0");0))

Erläuterung der Formel

=INDEX()B5:B16,MATCH("*"&E5&„*",)TEXT(B5:B16,"0"),0))

• VERGLEICH("*"&E5&„*",)TEXT(B5:B16;"0"),0)=5:Klicken Sie hier, um den detaillierten Ablauf anzuzeigen.
• INDEX()B5:B16,VERGLEICH("*"&E5&„*",)TEXT(B5:B16;"0"),0)) = INDEX(B5:B16 Die INDEX-Funktion gibt den 5. Wert im Rückgabebereich B5:B16 zurück, nämlich 34545.

Verwandte Funktionen

Verwandte Formeln