Формула на Excel: Текстът на съвпадението XLOOKUP съдържа -

Съдържание

Обща формула

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

Обобщение

За да използвате XLOOKUP за съвпадение на стойности, които съдържат специфичен текст, можете да използвате заместващи символи и конкатенация. В показания пример формулата във F5 е:

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

където код (B5: B15) и количество (C5: C15) са наречени диапазони.

Обяснение

Функцията XLOOKUP съдържа вградена поддръжка за заместващи символи, но тази функция трябва да бъде активирана изрично чрез задаване на режим на съвпадение на числото 2.

В показания пример XLOOKUP е конфигуриран да съответства на стойността, въведена в клетка E5, която може да се появи навсякъде в справочните стойности в B5: B15. Формулата във F5 е:

=XLOOKUP("*"&E5&"*",code,quantity,"no match",2) // returns 50
  • lookup_value - E5, със звездички (*), обединени отпред и отзад
  • lookup_array - наименованият код на диапазон (B5: B15)
  • return_array - наименованото количество диапазон (C5: C15)
  • if_not_found - низът "няма съвпадение"
  • match_mode - предоставя се като 2 (съвпадение на заместващ символ)
  • search_mode - не е предоставен. По подразбиране 1 (първо до последно)

За да се направи автоматично съвпадение на тип „съдържа“, звездата със заместващ знак (*) се добавя и добавя към стойността в клетка E5 с конкатенация:

"*"&E5&"*"

След конкатенацията формулата става:

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

XLOOKUP локализира първото съвпадение, което съдържа "BCC" (050-BCC-123 в ред 10) и връща съответната стойност от масива за връщане, 50.

Имайте предвид, че XLOOKUP не чувствителен към малки и големи букви, въвеждането на "bcc" в E5 ще върне същия резултат:

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

Вижте по-долу за опция за конфигуриране на XLOOKUP за съвпадение на регистъра.

Опция VLOOKUP

Формулата VLOOKUP също така поддържа заместващи символи, когато е зададена на точно съвпадение. Еквивалентната формула VLOOKUP за този пример е:

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

Пълно обяснение тук.

С ТЪРСЕНЕ и НАМЕР

Също така е възможно да използвате функциите SEARCH и FIND, за да извършите съвпадение на типа "съдържа" с XLOOKUP. За съвпадение без регистър (като примера по-горе), можете да използвате ТЪРСЕНЕ по следния начин:

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

За съвпадение с малки и големи букви можете вместо това да използвате FIND:

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

И двете опции по-горе улесняват разширяването на критериите, за да включват други условия, използвайки логическа логика.

Тук е обяснена логиката за ISNUMBER + SEARCH.

Множество съвпадения

Ако имате нужда от множество съвпадения, вижте функцията FILTER.

Интересни статии...