Generička formula
=XLOOKUP("*"&value&"*",lookup,results,,2)
Sažetak
Da biste koristili XLOOKUP za podudaranje vrijednosti koje sadrže određeni tekst, možete koristiti zamjenske znakove i spajanje. U prikazanom primjeru formula u F5 je:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
gdje su kod (B5: B15) i količina (C5: C15) imenovani rasponi.
Obrazloženje
Funkcija XLOOKUP sadrži ugrađenu podršku za zamjenske znakove, ali ova značajka mora biti izričito omogućena postavljanjem načina podudaranja na broj 2.
U prikazanom primjeru XLOOKUP je konfiguriran tako da odgovara vrijednosti unesenoj u ćeliju E5, koja se može pojaviti bilo gdje u vrijednostima pretraživanja u B5: B15. Formula u F5 je:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, sa zvjezdicama (*) spojenim sprijeda i straga
- lookup_array - imenovani kod raspona (B5: B15)
- return_array - imenovana veličina raspona (C5: C15)
- if_not_found - niz "ne podudara se"
- match_mode - pod uvjetom da je 2 (podudaranje zamjenskog znaka)
- search_mode - nije naveden. Zadane vrijednosti 1 (prva do zadnja)
Da bi se automatski podudaralo tip "sadrži", zamjenska zvjezdica (*) dodaje se i dodaje vrijednosti u ćeliji E5 s spajanjem:
"*"&E5&"*"
Nakon spajanja, formula postaje:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP pronalazi prvo podudaranje koje sadrži "BCC" (050-BCC-123 u retku 10) i vraća odgovarajuću vrijednost iz povratnog polja 50.
Imajte na umu da XLOOKUP ne razlikuje velika i mala slova, unos "bcc" u E5 vratit će isti rezultat:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
U nastavku pogledajte opciju za konfiguriranje XLOOKUP-a za podudaranje velikih i malih slova.
Opcija VLOOKUP
VLOOKUP formula također podržava zamjenske znakove kada je postavljeno na točno podudaranje. Ekvivalentna VLOOKUP formula za ovaj primjer je:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Potpuno objašnjenje ovdje.
Uz TRAŽENJE i PRONAĐI
Također je moguće koristiti funkcije SEARCH i FIND za izvođenje podudaranja tipa "sadrži" s XLOOKUP-om. Za podudaranje bez velikih i malih slova (poput gornjeg primjera), možete koristiti SEARCH na sljedeći način:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Za podudaranje velikih i malih slova umjesto toga možete upotrijebiti FIND:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Obje gornje opcije olakšavaju proširivanje kriterija na druge uvjete pomoću logičke logike.
Ovdje je objašnjena logika ISUMBER + SEARCH.
Višestruke utakmice
Ako trebate više podudaranja, pogledajte funkciju FILTER.
