Generisk formel
=XLOOKUP("*"&value&"*",lookup,results,,2)
Sammanfattning
Om du vill använda XLOOKUP för att matcha värden som innehåller specifik text kan du använda jokertecken och sammankoppling. I exemplet som visas är formeln i F5:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
där kod (B5: B15) och kvantitet (C5: C15) heter områden.
Förklaring
XLOOKUP-funktionen innehåller inbyggt stöd för jokertecken, men den här funktionen måste aktiveras uttryckligen genom att ställa in matchningsläge till nummer 2.
I det visade exemplet är XLOOKUP konfigurerad för att matcha det värde som anges i cell E5, vilket kan visas var som helst i uppslagsvärdena i B5: B15. Formeln i F5 är:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, med asterisker (*) sammankopplade fram och bak
- uppslagnings - det namngivna området koden (B5: B15)
- return_array - det namngivna området kvantitet (C5: C15)
- if_not_found - strängen "ingen matchning"
- match_mode - tillhandahålls som 2 (jokerteckenmatchning)
- search_mode - tillhandahålls inte. Standardvärde är 1 (första till sista)
För att göra en "innehåller" -typsmatch automatisk är jokertecken-asterisken (*) både förberedd och läggs till värdet i cell E5 med sammanfogning:
"*"&E5&"*"
Efter sammankoppling blir formeln:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP lokaliserar den första matchningen som innehåller "BCC" (050-BCC-123 i rad 10) och returnerar motsvarande värde från returmatrisen, 50.
Observera att XLOOKUP inte är skiftlägeskänsligt, om du anger "bcc" i E5 kommer samma resultat att returneras:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Se nedan för ett alternativ för att konfigurera XLOOKUP för en skiftlägeskänslig matchning.
VLOOKUP-alternativ
VLOOKUP-formeln stöder också jokertecken när de är inställda på exakt matchning. Motsvarande VLOOKUP-formel för detta exempel är:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Fullständig förklaring här.
Med SÖK och HITTA
Det är också möjligt att använda SÖK- och HITTA-funktionerna för att utföra en "innehåller" -typsmatchning med XLOOKUP. För en skiftlägeskänslig matchning (som exemplet ovan) kan du använda SÖK så här:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
För en skiftlägeskänslig matchning kan du använda HITTA istället:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Båda alternativen ovan gör det lättare att utvidga kriterierna till att omfatta andra villkor med boolesk logik.
Logiken för ISNUMBER + SEARCH förklaras här.
Flera matchningar
Om du behöver flera matchningar, se FILTER-funktionen.
