Generische Formel
=XLOOKUP("*"&value&"*",lookup,results,,2)
Zusammenfassung
Um XLOOKUP zum Abgleichen von Werten zu verwenden, die bestimmten Text enthalten, können Sie Platzhalter und Verkettungen verwenden. In dem gezeigten Beispiel lautet die Formel in F5:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
Dabei werden Code (B5: B15) und Menge (C5: C15) als Bereiche bezeichnet.
Erläuterung
Die XLOOKUP-Funktion enthält integrierte Unterstützung für Platzhalter. Diese Funktion muss jedoch explizit aktiviert werden, indem der Übereinstimmungsmodus auf die Nummer 2 gesetzt wird.
In dem gezeigten Beispiel ist XLOOKUP so konfiguriert, dass es mit dem in Zelle E5 eingegebenen Wert übereinstimmt, der an einer beliebigen Stelle in den Suchwerten in B5: B15 erscheinen kann. Die Formel in F5 lautet:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, mit Sternchen (*) vorne und hinten verkettet
- Suchmatrix - der benannte Bereich Code (B5: B15)
- return_array - die benannten Bereich Menge (C5: C15)
- if_not_found - die Zeichenfolge "no match"
- match_mode - wird als 2 bereitgestellt (Platzhalterübereinstimmung)
- search_mode - nicht angegeben. Der Standardwert ist 1 (erster bis letzter)
Damit eine Übereinstimmung vom Typ "enthält" automatisch erfolgt, wird das Platzhalter-Sternchen (*) mit Verkettung an den Wert in Zelle E5 angehängt und an diesen angehängt:
"*"&E5&"*"
Nach der Verkettung lautet die Formel:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
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 bei XLOOKUP nicht zwischen Groß- und Kleinschreibung unterschieden wird. Wenn Sie in E5 "bcc" eingeben, wird das gleiche Ergebnis zurückgegeben:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Im Folgenden finden Sie eine Option zum Konfigurieren von XLOOKUP für eine Übereinstimmung zwischen Groß- und Kleinschreibung.
VLOOKUP-Option
Die VLOOKUP-Formel unterstützt auch Platzhalter, wenn sie genau übereinstimmen. Die äquivalente VLOOKUP-Formel für dieses Beispiel lautet:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Vollständige Erklärung hier.
Mit SUCHEN und FINDEN
Es ist auch möglich, die Funktionen SEARCH und FIND zu verwenden, um eine Übereinstimmung vom Typ "enthält" mit XLOOKUP durchzuführen. Für eine Übereinstimmung ohne Berücksichtigung der Groß- und Kleinschreibung (wie im obigen Beispiel) können Sie SEARCH wie folgt verwenden:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Für eine Übereinstimmung zwischen Groß- und Kleinschreibung können Sie stattdessen FIND verwenden:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Beide oben genannten Optionen erleichtern das Erweitern von Kriterien auf andere Bedingungen mithilfe der Booleschen Logik.
Die Logik für ISNUMBER + SEARCH wird hier erklärt.
Mehrere Übereinstimmungen
Wenn Sie mehrere Übereinstimmungen benötigen, lesen Sie die Funktion FILTER.
