Formula generică
=XLOOKUP("*"&value&"*",lookup,results,,2)
rezumat
Pentru a utiliza XLOOKUP pentru a potrivi valori care conțin un anumit text, puteți utiliza metacaractere și concatenare. În exemplul prezentat, formula din F5 este:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)
unde codul (B5: B15) și cantitatea (C5: C15) sunt denumite intervale.
Explicaţie
Funcția XLOOKUP conține suport încorporat pentru metacaracere, dar această caracteristică trebuie activată în mod explicit setând modul de potrivire la numărul 2.
În exemplul prezentat, XLOOKUP este configurat pentru a se potrivi cu valoarea introdusă în celula E5, care poate apărea oriunde în valorile de căutare din B5: B15. Formula din F5 este:
=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
- lookup_value - E5, cu asteriscuri (*) concatenate față și spate
- lookup_array - codul intervalului denumit (B5: B15)
- return_array - cantitatea de interval denumită (C5: C15)
- if_not_found - șirul „nu se potrivește”
- match_mode - furnizat ca 2 (match wildcard)
- search_mode - nu este furnizat. Valori implicite la 1 (de la primul până la ultimul)
Pentru a face o potrivire automată de tip „conține”, asteriscul cu caractere wild (*) este atât anexat, cât și atașat la valoarea din celula E5 cu concatenare:
"*"&E5&"*"
După concatenare, formula devine:
=XLOOKUP("*BCC*",code,quantity,"no match",2)
XLOOKUP localizează prima potrivire care conține „BCC” (050-BCC-123 în rândul 10) și returnează valoarea corespunzătoare din matricea returnată, 50.
Rețineți că XLOOKUP nu este sensibil la majuscule și minuscule, dacă introduceți „bcc” în E5 se va obține același rezultat:
=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50
Vedeți mai jos pentru o opțiune de configurare XLOOKUP pentru o potrivire sensibilă la majuscule.
Opțiunea VLOOKUP
Formula VLOOKUP acceptă și metacaracterele atunci când este setată la potrivirea exactă. Formula VLOOKUP echivalentă pentru acest exemplu este:
=VLOOKUP("*"&E5&"*",B5:C15,2,0)
Explicație completă aici.
Cu CĂUTARE și GĂSIRE
De asemenea, este posibil să utilizați funcțiile SEARCH și FIND pentru a efectua o potrivire de tip „conține” cu XLOOKUP. Pentru o potrivire care nu face sensibilitatea la majuscule și minuscule (cum ar fi exemplul de mai sus), puteți utiliza SEARCH astfel:
=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)
Pentru o potrivire sensibilă la majuscule, puteți folosi FIND în schimb:
=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)
Ambele opțiuni de mai sus facilitează extinderea criteriilor pentru a include alte condiții folosind logica booleană.
Logica pentru ISNUMBER + SEARCH este explicată aici.
Meciuri multiple
Dacă aveți nevoie de mai multe potriviri, consultați funcția FILTER.
