Generisk formel
=XLOOKUP("*"&value&"*",lookup,results,,2)
Sammendrag
For å bruke XLOOKUP til å matche verdier som inneholder spesifikk tekst, kan du bruke jokertegn og sammenkobling. I eksemplet vist er formelen i F5:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
der kode (B5: B15) og mengde (C5: C15) er kalt områder.
Forklaring
XLOOKUP-funksjonen inneholder innebygd støtte for jokertegn, men denne funksjonen må aktiveres eksplisitt ved å stille samsvarsmodus til nummer 2.
I eksemplet som er vist er XLOOKUP konfigurert til å matche verdien som er angitt i celle E5, som kan vises hvor som helst i oppslagsverdiene i B5: B15. Formelen i F5 er:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, med stjerner (*) sammenkoblet foran og bak
- lookup_array - den navngitte områdekoden (B5: B15)
- return_array - den navngitte området mengde (C5: C15)
- if_not_found - strengen "no match"
- match_mode - gitt som 2 (jokertegnkamp)
- search_mode - ikke oppgitt. Standardinnstillingen er 1 (først til siste)
For å lage en "inneholder" -type samsvar automatisk, er wildcard-stjernen (*) både forhåndsbetalt og lagt til verdien i celle E5 med sammenkobling:
"*"&E5&"*"
Etter sammenkobling blir formelen:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP lokaliserer den første kampen som inneholder "BCC" (050-BCC-123 i rad 10) og returnerer den tilsvarende verdien fra returmatrisen, 50.
Vær oppmerksom på at XLOOKUP ikke er mellom store og små bokstaver, når du skriver inn "bcc" i E5, får du det samme resultatet:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Se nedenfor for et alternativ for å konfigurere XLOOKUP for en saksfølsom kamp.
VLOOKUP-alternativet
VLOOKUP-formelen støtter også jokertegn når den er satt til nøyaktig samsvar. Den tilsvarende VLOOKUP-formelen for dette eksemplet er:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Full forklaring her.
Med SØK og FINN
Det er også mulig å bruke SØK- og FINN-funksjonene til å utføre en "inneholder" -typekamp med XLOOKUP. For en sakuensensitiv kamp (som eksemplet ovenfor), kan du bruke SØK slik:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
For en saksfølsom kamp kan du bruke FIND i stedet:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Begge alternativene ovenfor gjør det lettere å utvide kriteriene til å omfatte andre forhold ved hjelp av boolsk logikk.
Logikken for ISNUMBER + SEARCH er forklart her.
Flere kamper
Hvis du trenger flere treff, se FILTER-funksjonen.
