Excel vzorec: text zhody XLOOKUP obsahuje -

Obsah

Všeobecný vzorec

=XLOOKUP("*"&value&"*",lookup,results,,2)

Zhrnutie

Ak chcete použiť XLOOKUP na priradenie hodnôt obsahujúcich konkrétny text, môžete použiť zástupné znaky a zreťazenie. V zobrazenom príklade je vzorec v F5:

=XLOOKUP("*"&E5&"*",code,quantity,"no match",2)

kde kód (B5: B15) a množstvo (C5: C15) sú pomenované rozsahmi.

Vysvetlenie

Funkcia XLOOKUP obsahuje vstavanú podporu zástupných znakov, ale táto funkcia musí byť povolená výslovne nastavením režimu zhody na číslo 2.

V zobrazenom príklade je XLOOKUP nakonfigurovaný tak, aby zodpovedal hodnote zadanej v bunke E5, ktorá sa môže zobraziť kdekoľvek vo vyhľadávacích hodnotách v B5: B15. Vzorec v F5 je:

=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
  • lookup_value - E5, s hviezdičkami (*) zreťazenými vpredu a vzadu
  • lookup_array - pomenovaný kód rozsahu (B5: B15)
  • return_array - pomenované množstvo rozsahu (C5: C15)
  • if_not_found - reťazec „žiadna zhoda“
  • match_mode - poskytnuté ako 2 (zástupný znak)
  • režim hľadania - neposkytuje sa. Predvolené hodnoty 1 (od prvého po posledný)

Ak chcete, aby sa zhoda typu „obsahuje“ automaticky, je pred znak zástupnej hviezdičky (*) pridaná aj pripojená hodnota k bunke E5 so zreťazením:

"*"&E5&"*"

Po zreťazení vznikne vzorec:

=XLOOKUP("*BCC*",code,quantity,"no match",2)

XLOOKUP vyhľadá prvú zhodu, ktorá obsahuje „BCC“ (050-BCC-123 v riadku 10), a vráti príslušnú hodnotu z návratového poľa, 50.

Upozorňujeme, že v XLOOKUPE sa nerozlišujú veľké a malé písmená, zadanie hodnoty „bcc“ do E5 vráti rovnaký výsledok:

=XLOOKUP("*bcc*",code,quantity,"no match",2) // returns 50

Nižšie uvádzame možnosť konfigurácie XLOOKUPU na zhodu veľkých a malých písmen.

Možnosť VLOOKUP

Vzorec VLOOKUP tiež podporuje zástupné znaky, ak je nastavený na presnú zhodu. Ekvivalentný vzorec VLOOKUP pre tento príklad je:

=VLOOKUP("*"&E5&"*",B5:C15,2,0)

Úplné vysvetlenie tu.

Pomocou HĽADAŤ a NÁJDIŤ

Na vykonanie zhody typu „obsahuje“ s XLOOKUP je tiež možné použiť funkcie SEARCH a FIND. Pre zhodu bez rozlišovania malých a veľkých písmen (ako v príklade vyššie) môžete použiť VYHĽADÁVANIE takto:

=XLOOKUP(1,--ISNUMBER(SEARCH("BCC",code)),quantity,"no match",2)

Pre zhodu s malými a veľkými písmenami môžete namiesto toho použiť NÁJDIŤ:

=XLOOKUP(1,--ISNUMBER(FIND("BCC",code)),quantity,"no match",2)

Obe možnosti vyššie uľahčujú rozšírenie kritérií tak, aby zahŕňali ďalšie podmienky, pomocou logickej logiky.

Logika pre ISNUMBER + SEARCH je vysvetlená tu.

Viac zápasov

Ak potrebujete viac zhôd, pozrite si funkciu FILTER.

Zaujímavé články...