XLOOKUP-Match-Text enthält

Um XVERWEIS zu verwenden, um Werte abzugleichen, die bestimmten Text enthalten, können Sie Platzhalter und Verkettung . Im gezeigten Beispiel lautet die Formel in F5:

= XLOOKUP ('*'&value&'*',lookup,results,,2)

wo Code (B5:B15) und Anzahl (C5:C15) sind benannte Bereiche .

Die XLOOKUP-Funktion enthält integrierte Unterstützung für Platzhalter, diese Funktion muss jedoch explizit aktiviert werden, indem der Match-Modus auf die Zahl 2 gesetzt wird.

wie man Durchschnittswerte in Excel findet

Im gezeigten Beispiel ist XLOOKUP so konfiguriert, dass es dem in Zelle E5 eingegebenen Wert entspricht, der an beliebiger Stelle in den Nachschlagewerten in B5:B15 erscheinen kann. Die Formel in F5 lautet:

 
= XLOOKUP ('*'&E5&'*',code,quantity,'no match',2)

• Lookup-Wert - E5, mit Sternchen (*) vorne und hinten verkettet
• lookup_array - der benannte Bereich Code (B5:B15)
• return_array - der benannte Bereich Anzahl (C5:C15)
• if_not_found - die Zeichenfolge 'keine Übereinstimmung'
• match_mode - als 2 angegeben (Wildcard-Match)
• Suchmodus - nicht vorgesehen. Standardmäßig 1 (erster bis letzter)

Um eine Typübereinstimmung vom Typ 'enthält' automatisch zu machen, wird das Platzhalter-Sternchen (*) sowohl vorangestellt als auch an den Wert in Zelle E5 mit . angehängt Verkettung :

 
= XLOOKUP ('*'&E5&'*',code,quantity,'no match',2) // returns 50

Nach der Verkettung lautet die Formel:

 
'*'&E5&'*'

XLOOKUP sucht die erste Übereinstimmung, die 'BCC' enthält (050-BCC-123 in Zeile 10) und gibt den entsprechenden Wert aus dem Rückgabearray 50 zurück.

Beachten Sie, dass XLOOKUP nicht Groß- und Kleinschreibung beachten, die Eingabe von 'bcc' in E5 liefert das gleiche Ergebnis:

 
= XLOOKUP ('*BCC*',code,quantity,'no match',2)

Unten finden Sie eine Option zum Konfigurieren von XLOOKUP für eine Übereinstimmung mit Groß-/Kleinschreibung.

SVERWEIS-Option

Die SVERWEIS-Formel unterstützt auch Platzhalter, wenn sie auf exakte Übereinstimmung eingestellt ist. Die entsprechende SVERWEIS-Formel für dieses Beispiel lautet:

 
= XLOOKUP ('*bcc*',code,quantity,'no match',2) // returns 50

Voll Erklärung hier .

Mit SUCHEN und FINDEN

Es ist auch möglich, die SUCHE und FINDEN Funktionen, um eine Übereinstimmung vom Typ 'enthält' mit XLOOKUP durchzuführen. Für eine Übereinstimmung ohne Beachtung der Groß-/Kleinschreibung (wie im obigen Beispiel) können Sie SEARCH wie folgt verwenden:

 
= VLOOKUP ('*'&E5&'*',B5:C15,2,0)

Für eine Übereinstimmung mit Groß-/Kleinschreibung können Sie stattdessen FIND verwenden:

 
= XLOOKUP (1,-- ISNUMBER ( SEARCH ('BCC',code)),quantity,'no match',2)

Beide oben genannten Optionen erleichtern die Erweiterung der Kriterien auf andere Bedingungen einschließen mit Boolesche Logik .

Die Logik für ISNUMBER + SEARCH ist hier erklärt .

Mehrere Spiele

Wenn Sie mehrere Übereinstimmungen benötigen, lesen Sie die FILTER-Funktion .

Excel-Formel zur Berechnung des zukünftigen Investitionswerts